Forum Discussion
Dates Formatting Will Not Update When Changed
I am attempting to change the date format from the traditional U.S. date format (January 1, 2021) to MM/DD/YYYY (01/01/2021). When I select this, even under custom format, and apply to the selected cells, nothing happens. The only way I can get it to update is if I double click on the cell and hit enter. Any help regarding this issue would be greatly appreciated.
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.
8 Replies
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!
NFMorgan Are you sure that the "dates" are real dates? I suspect that they are texts. Try entering a formula (anywhere) like =ISNUMBER(cell), where "cell" is a reference to a cell with a "date". If it returns FALSE, you are dealing with texts. No matter how you format these, they will always be texts and never convert till real date formats.
- Looks like I am dealing with text. Does this mean there is no way to rectify the issue? Is it possible that I could turn it into a value using the =VALUE(cell) function?
