SOLVED

Excel stopped formatting dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2398233%22%20slang%3D%22en-US%22%3EExcel%20stopped%20formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398233%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20Excel%20just%20stopped%20formatting%20dates.%20Even%20if%20I%20select%20the%20column%20and%20use%20the%20format%20cells%20command%20again%20it%20does%20not%20work.%20I%20recently%20changed%20windows%20language%20from%20portuguese%20to%20english%2C%20maybe%20this%20had%20an%20influence.%20I%20submitted%20this%20post%20before%2C%20but%20I%20am%20submitting%20it%20again%2C%20because%20I%20noticed%20that%20it%20happens%20only%20on%20the%20Date%20formatting.%20I%20am%20attaching%20the%20file.%20First%20column%20is%20a%20date.%20I%20am%20an%20university%20professor%20and%20use%20this%20command%20a%20lot%20to%20change%20dates%20into%20numbers%20to%20grade%20students%20by%20their%20task%20delivery%20date.%20Can%20someone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2398233%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398250%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20stopped%20formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1066166%22%20target%3D%22_blank%22%3E%40LuisAraujo1948%3C%2FA%3E%26nbsp%3BNot%20sure%20how%20you%20enter%20the%20dates.%20When%20I%20select%20the%20cells%20at%20the%20bottom%20one-by-one%20and%20press%20Enter%2C%20they%20switch%20to%20the%20desired%20date%20format.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-29%20at%2018.52.46.png%22%20style%3D%22width%3A%20171px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284839i983B06F0C4F0AD21%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-29%20at%2018.52.46.png%22%20alt%3D%22Screenshot%202021-05-29%20at%2018.52.46.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398256%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20stopped%20formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1066166%22%20target%3D%22_blank%22%3E%40LuisAraujo1948%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20you%20Windows%20settings.%20Try%20to%20enter%20%2229%2F5%22%20instead%20of%20%2229-5%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398373%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20stopped%20formatting%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398373%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20Formatting%20is%2029-mai-21%2C%20I%20entered%2029%2F5%20and%20remained%20the%20same%2C%2029%2F5.%3C%2FLINGO-BODY%3E
Occasional Contributor

My Excel just stopped formatting dates. Even if I select the column and use the format cells command again it does not work. I recently changed windows language from portuguese to english, maybe this had an influence. I submitted this post before, but I am submitting it again, because I noticed that it happens only on the Date formatting. I am attaching the file. First column is a date. I am an university professor and use this command a lot to change dates into numbers to grade students by their task delivery date. Can someone help?

10 Replies

@LuisAraujo1948 Not sure how you enter the dates. When I select the cells at the bottom one-by-one and press Enter, they switch to the desired date format.

Screenshot 2021-05-29 at 18.52.46.png

@LuisAraujo1948 

It depends on you Windows settings. Try to enter "29/5" instead of "29-5"

Thank you very much. Formatting is 29-mai-21, I entered 29/5 and remained the same, 29/5.

@LuisAraujo1948 

It doesn't work for any file or only for this specific one?

Thank you very much. All files.
Microsoft is asking me to choose one as best answer, but that would be unfair, as I solved the issue myself. I am truly grateful to everyone that has tried to solve, everyone have a great day and a wonderful weeekend.

@LuisAraujo1948 

Don't care about best answers, but it'll be useful for other people if you share how did you solve you issue. And you mark your own answer as best.

best response confirmed by LuisAraujo1948 (Occasional Contributor)
Solution
It was a regional setting. I changed Windows language from Portuguese to English. Here in Brazil we use dates as dd/mm/yy, and in the USA it is mm/dd/yy. So, when in Portuguese we enter date as 4-5, date goes as May 4th. We were at the end of the month, so I was entering 25-5 (expecting May 25th), and as there is no 25th month, Excel just did not change that. When June began, it was easy to guess, as I entered 1-6 and it was formatted as January 6th. Thanks again.

@LuisAraujo1948 

Great, thank for sharing. I only don't catch why 24-5 works (as in your file) and 25-5 doesn't work. Or in the sample file you changed dates manually?

Probably because May 24 Windows language was Portuguese, and in the 25th I changed it to English.