Adding a common prefix to date value in a column

[phani.gamini]

Adding a common prefix to date value in a column.


9-Jan-11 '40552'

17-Jan-11 '40560'

25-Jan-11 '40568'

2-Feb-11 '40576'

10-Feb-11 '40584'

18-Feb-11 '40592'


When I tried to add single Quote before and after the date, it is changed as above.


I want the date 9-Jan-11 as '9-Jan-11',I want to add the single quotes before and after the date for all values in a single column.

 

[jason]

the numbers incased in the single quotes is the number behind that said date.... (Jan 9 2011 is 40552 days after 1/1/00)


when i added a single quote before and after all i got was 9-Jan-11'

but what youre saying is you want the value of the cell to '9-Jan-11', correct?


what if you just changed the number formatting to accomplish this? that way when you enter a date in the cell, the single quoates will be displayed (but not technically stored in the cell). does that make sense? if that would work:


select the cell(s) with the date --> goto Format Cells --> Number Format tab --> Custom --> and enter 'd-mmm-yy'

 

[Kevin@Radstock]

Hi phani.gamini


Not quite sure if I understand the question. You want the date to display within apostrophe at each end, it will not be a date then!


Assuming your data is in column A in column B an copy down:

=TEXT(A1,"'dd-mmm-yy'")

 

[Faseeh]

Hi phani.gamini


You can also custom format your dates, select the range and adopt "'"d-mmm-yy"'" as custom cell format.


Regards,

 

[shrivallabha]

Hi phani.gamini,


What are you really after? Please explain the purpose as that will give you solution that suits you.