Jul 19 2021 12:15 PM
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.
Jul 19 2021 12:23 PM
@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.
Jul 19 2021 12:24 PM
SolutionThat means the values are probably not 'real' dates but text values that look like dates.
Try the following:
Selection.Value = Selection.Value
Jul 19 2021 12:27 PM
Jul 19 2021 12:31 PM
Jan 26 2022 03:01 PM
Jan 26 2022 03:38 PM
Jul 11 2022 01:07 PM
Jan 03 2024 07:32 PM - edited Jan 03 2024 07:35 PM
(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.
Jul 19 2021 12:24 PM
SolutionThat means the values are probably not 'real' dates but text values that look like dates.
Try the following:
Selection.Value = Selection.Value