Dates Formatting Will Not Update When Changed

[NFMorgan]

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.

[#2]

@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.

[#3 · Accepted solution]

@NFMorgan 

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.

[#4]

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?

[#5]

This worked perfectly, thank you very much!

[#6]

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!

[#7]

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

[#8]

I don't understand why this works but wow does it ever! Thanks for posting this!