SOLVED

Dates Formatting Will Not Update When Changed

%3CLINGO-SUB%20id%3D%22lingo-sub-2563568%22%20slang%3D%22en-US%22%3EDates%20Formatting%20Will%20Not%20Update%20When%20Changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563568%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20change%20the%20date%20format%20from%20the%20traditional%20U.S.%20date%20format%20(January%201%2C%202021)%20to%20MM%2FDD%2FYYYY%20(01%2F01%2F2021).%20When%20I%20select%20this%2C%20even%20under%20custom%20format%2C%20and%20apply%20to%20the%20selected%20cells%2C%20nothing%20happens.%20The%20only%20way%20I%20can%20get%20it%20to%20update%20is%20if%20I%20double%20click%20on%20the%20cell%20and%20hit%20enter.%20Any%20help%20regarding%20this%20issue%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2563568%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563593%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Formatting%20Will%20Not%20Update%20When%20Changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1107435%22%20target%3D%22_blank%22%3E%40NFMorgan%3C%2FA%3E%26nbsp%3BAre%20you%20sure%20that%20the%20%22dates%22%20are%20real%20dates%3F%20I%20suspect%20that%20they%20are%20texts.%20Try%20entering%20a%20formula%20(anywhere)%20like%20%3DISNUMBER(cell)%2C%20where%20%22cell%22%20is%20a%20reference%20to%20a%20cell%20with%20a%20%22date%22.%20If%20it%20returns%20FALSE%2C%20you%20are%20dealing%20with%20texts.%20No%20matter%20how%20you%20format%20these%2C%20they%20will%20always%20be%20texts%20and%20never%20convert%20till%20real%20date%20formats.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563594%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Formatting%20Will%20Not%20Update%20When%20Changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1107435%22%20target%3D%22_blank%22%3E%40NFMorgan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20means%20the%20values%20are%20probably%20not%20'real'%20dates%20but%20text%20values%20that%20look%20like%20dates.%3C%2FP%3E%0A%3CP%3ETry%20the%20following%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20the%20range%20with%20these%20'dates'.%3C%2FLI%3E%0A%3CLI%3EPress%20Alt%2BF11%20to%20activate%20the%20Visual%20Basic%20Editor.%3C%2FLI%3E%0A%3CLI%3EPress%20Ctrl%2BG%20to%20activate%20the%20Immediate%20window.%3C%2FLI%3E%0A%3CLI%3EType%20or%20copy%2Fpaste%20the%20following%20line%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESelection.Value%20%3D%20Selection.Value%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CUL%3E%0A%3CLI%3EWith%20the%20insertion%20point%20anywhere%20in%20that%20line%2C%20press%20Enter.%3C%2FLI%3E%0A%3CLI%3EClose%20the%20Visual%20Basic%20Editor.%3C%2FLI%3E%0A%3CLI%3ETry%20changing%20the%20number%20format%20again.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563602%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Formatting%20Will%20Not%20Update%20When%20Changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563602%22%20slang%3D%22en-US%22%3ELooks%20like%20I%20am%20dealing%20with%20text.%20Does%20this%20mean%20there%20is%20no%20way%20to%20rectify%20the%20issue%3F%20Is%20it%20possible%20that%20I%20could%20turn%20it%20into%20a%20value%20using%20the%20%3DVALUE(cell)%20function%3F%3C%2FLINGO-BODY%3E
New Contributor

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.

4 Replies

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

best response confirmed by NFMorgan (New Contributor)
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.

 

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?
This worked perfectly, thank you very much!