Forum Discussion
Dates Formatting Will Not Update When Changed
That means the values are probably not 'real' dates but text values that look like dates.
Try the following:
- Select the range with these 'dates'.
- Press Alt+F11 to activate the Visual Basic Editor.
- Press Ctrl+G to activate the Immediate window.
- Type or copy/paste the following line:
Selection.Value = Selection.Value- With the insertion point anywhere in that line, press Enter.
- Close the Visual Basic Editor.
- Try changing the number format again.
That means the values are probably not 'real' dates but text values that look like dates.
Try the following:
- Select the range with these 'dates'.
- Press Alt+F11 to activate the Visual Basic Editor.
- Press Ctrl+G to activate the Immediate window.
- Type or copy/paste the following line:
Selection.Value = Selection.Value- With the insertion point anywhere in that line, press Enter.
- Close the Visual Basic Editor.
- Try changing the number format again.
(Meant to reply to NFMorgan)
The [Alt+F11, Ctrl+G, type Selection.Value = Selection.Value, press Enter] solution had no effect.
Before and after using it, I tried converting to Number first, then to Date. None of the date formats worked. This was done in a new spreadsheet with the numbers being typed into the cells. Nothing was copied in from another document or source.
Then I tried creating another new file, but had the same results.
After much tinkering, I discovered the problem. EXCEL CANNOT APPLY A DATE FORMAT TO ANY DATE EARLIER THAN 1/1/1900. Tried dates from that date forward, and they format perfectly. Tried a date in 1899...it remains unformatted. So worth knowing that helpful fancy formatting has this built-in limitation. I do a lot of genealogy work, and this is a problem...but probably not one that MS will fix.
- I don't understand why this works but wow does it ever! Thanks for posting this!
- I have no idea what any of that means (VERY basic Excel user here) but I had the exact same problem. I was trying to import bank transactions into Quick Books with a CSV file but the date format was wrong and created an error when attempting to upload the file. But the date style wouldn't change when trying to format the column. I followed your instructions (like I don't even know what Visual Basic Editor is or why it opened up with pressing Alt + F11) and it totally worked! Thank you for sharing your Excel sorcery skills!
- This worked perfectly, thank you very much!
- Good evening, Ask Excel to consider your cells as number first
1) enter 1 in a cell
2) copy it
3) select your dates range
4) edit paste special multiply you obtain numbers
5) apply a date format
Regards
