changing dates to text

[Shaun]

Hi All


I am trying (without success) to convert a list of dates (21/07/2012 format) but when I select a cell containing the a date and change to format to text I get 41111.


When I place a ' in front I obtain the correct result, text in the format of 21/07/2012.


I need a VBA solution where it will select a range of dates and place a ' at the start of each date in the range. All dates are in column A.


I am stumped.


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


Can you please specify your problem exactly ?


You have a range of dates in dd/mm/yyyy format ; what do you want these dates to be converted to ?


Dates are basically numbers ; a date such as today , will display as 07/21/2012 if the date format is mm/dd/yyyy ; it will display as 21/07/2012 if you select a dd/mm/yyyy format ; if you select any other format such General , Number or even Text , it will display the base value , which is numeric ; in this case , it will be 41111.


If you want to convert the numeric value to text , then use the =TEXT(date_value,"dd/mm/yyyy") formula to convert the numeric date value to text.


Narayan

 

[Debraj]

Hi Shaun..

Did you ever tried some stupid things to make excel fool..

I am doing this regularly..


Cut the Date Range..

Paste it in Notepad.. Office is basically WYSIWYG (What you see is what you get)

So, now in Notepad everything is just a Text.


Come back to Excel.. Format the cut-ted area as TEXT.

Now again come back to Notepad.. Copy all the data(Ctrl + A) and Paste it in the same area..

Excel knows how many cell need to left or need to full..


If now Excel ask you to 'Change to Date Format' or show you a Exclamation Icon in the top-left area..

Just say..

#@$% &^$

(Ignore Error)..

 

[Haseeb A]

You could also use Text to column.


Select the dates, go to Data >> Text to columns >> Next >> Next, then from the top left side, select 'Text' & click Finish.