Excel calculated Date in erratic way

[vsinha]

I have a cell which contains the value 2/28/1996 & the cell is formatted as DATE type.

Now I want to add ' symbol before & after this value in a new cell to make it look like '2/28/1996'.


To do this I tried two things-

1) =CHAR(39) & $I1 & CHAR(39)

2) =CONCATENATE("'",I1,"'")


In both cases, Excel somehow converts the date to 35123 & results in '35123'. I have also set the calculations to manual mode.


I need to do this with a number of values, & I can't do it manually.

A quick response would be greatly appreciated.


Thanks in anticipation guys..!!


--Vikas

 

[NARAYANK991]

Hi Vikas ,


Try the following , and let me know the result :


="'"&TEXT(I1,"mm/dd/yyyy")&"'"


Of course , instead of the & operator , you can also use the CONCATENATE function as follows :


=CONCATENATE("'",TEXT(I1,"mm/dd/yyyy"),"'")

 

[vsinha]

Works like a charm..

Thanks a lot my dear friend..

 

[NARAYANK991]

Hi Vikas ,


Welcome. Thanks for the feedback.


Narayan