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.