Dead date format

[samitnair]

Hi XL Geeks

Once again am in need of your expert comments and advices.

I have a column with dates entered as 25.01.10 (DD.MM.YY) without any specific formating done. Am now trying to change the format into 25. January.2010 (DD.MMMM.YYYY), but the date or the column seems to be dead in terms to formating like format as number/custom/date/general anything no change to the date style.

i tried the following steps

1. opened another new xl sheet and tried to enter a date in 25.01.10 and then change it to 25.JANUARY.2010 (IT WORKS)
2. Open another new xl sheet
(a). Copy the date column and paste in new sheet and change format
(b). Cut the date column and paste in new sheet and change format
(c). Change the format of the column and the paste
3. Open a notepad, cut the column paste in notepad, repasted in same sheet
4. copy the column paste special as text and Unicode text
5. Tried different types of format.
6. Split the date into 3 columns using text to columns and rejoined it with "&" function and the tried formating
7. Edit--Clear format
8. Delete the column and entered date manually as 25.01.10 and tried formating
9. Tried on another system and with office 2010 (currently using 03)
10. Tried on another column to enter date and change format
9. NO VB NO RESTRICTION in the sheet or workbook
NO CHANGE the sheet remains dead

Am running out of resources and am stuck. Please let me know if am missing something. Pleas guide thanks

 

[Jon von der Heyden]

Hello

Highlight the column of dates
Invoke Find & Replace (Ctrl+H)
Find what: .
Replace with: /

Once you have replaced the dots with a slash you should be able to format the column to the desired date format.

 

[samitnair]

Thank you for the reply...It worked but worked only for certain cells (25.01.10 to 25/01/10 after format 25 January 2010)...all other cells in th column remains 25/01/10

 

[Jon von der Heyden]

Hello

Highlight the column
Invoke Text to Columns (Text menu in 97-2003, Data tab 2007+)
Choose delimited > Next
Clear all Delimiters
Hit Finish

 

[samitnair]

I TRIED DELIMITING

1. Select the date column
2. Click on text to columns
3. Select Delimited click next
4. uncheck all delimiters, check others and Enter "/"
5. uncheck "treat consequtive delimiters as one"
6. Text Qualifiers --- None
7. Next
8. Column data format "General"
9. destination three columns selected
10. Finish

Result: 25 01 2010 in three different columns (E9 F9 G9)
Used formula to reunite all =E9&"/"&F9&"/"&G9
result: 25/01/2010 but cell remains dead in format

 

[Jon von der Heyden]

Step 4 is where you went wrong. Uncheck all delimiters, leave other blank...