Weird Excel 2021 Date Formatting Not Working

[Excel User 2024]

Hi Folks - Long time lurker & excel user. I recently upgraded from 2016/Win7 to 2021/Win11.

For some reason when I enter a date into a cell it refuses to auto format it regardless of the settings. I've tried clearing the cell, formatting as multiple types of dates & even the custom formats with ddd-mmm-yyyy!

Normally if I plug in 3/26/24 it it would reformat it into 3/26/2024 automatically on every version that I've used since the 90's...did they do something or is it some weird setting that needs to be changed?

The cell itself isn't anything weird or coded - it's literally just a cell with a manually entered date to show when that sheet was updated. The install is the full version & registered (Microsoft® Excel® 2021 MSO (Version 2402 Build 16.0.17328.20124) 64-bit ). It's updated to the most current build & so is Win11.

The sheet does have pivots in it if that matters. There isn't any VBA or macros.

I hope someone has encountered this and has a solution. Everything I've tried via google hasn't worked and I'm sure its not some kind of Windows date coding feature interfering with it.

Thanks in advance!

 

[Fluff]

Is the cell formatted as text before you enter anything?
Also what happens if you type in 26/3/24

 

[Excel User 2024]

Thanks Fluff!

No, it wasn't formatted as text. I've tried all of the different formats including clearing the cell itself. I put in 26-3-24 and it converted the format correctly! Inputting the date as 3/24/24 is still broken though. Even 3/24/2024 still looks like text despite the date formatting. I'm using US English (forgot to mention that before).

 

[Dave Patton]

What are your system's Regional Settings for dates?

When you look at the bottom right of many computer displays, the date is shown.
What information shows with your system?

Enter 12-3 and press enter and then format to a date such as yyyy-mmm-dd.
What shows with your system?

 

[Excel User 2024]

Hi Dave - thanks for the response.

Timezone is set correctly for Eastern DST & the date is displayed as 26-MAR-24 on the taskbar.

I entered 12-3 in the cell and it automatically changed it to 12-Mar in the spreadsheet without formatting it afterwards. Maybe because it's an older sheet it somehow wasn't fully compatible with the new 2021 excel version. I guess I'll just have to remember to type the dates in a different format. Weird that they changed this behavior.

 

[Dave Patton]

Your Regional Settings Windows 11 are some version of International Settings. dd-mm-yy

You may want American settings that are mm-dd-yy.

The system that I am currently using is Windows 10. Windows 11 is similar but some of wording may be different.

In Windows Setting Time and Language Region Regional Settings and adjust to your preferences.

You could also get to the appropriate settings from Control Panel.

You stated "Maybe because it's an older sheet it somehow wasn't fully compatible with the new 2021 excel version.". I do not know what you mean with this comment.

If you are importing text dates, they may need to be converted.

Please provide information on your system setting and your preferences and requirements.

If your question relates to importing information, please provide examples and your expected results.

 

[Peter_SSs]

Further to what is written above: Check your Windows regional settings -> Short Date. I suspect that will show a 2-digit year given this ..

the date is displayed as 26-MAR-24 on the taskbar.

If so edit your regional settings short date to a 4-digit year & see what happens in Excel when you enter a date with a 2-digit year.

 

[Excel User 2024]

Thanks everyone - it looks like the windows settings were impacting excel. Changing the regional settings was the solution.

For Dave - I meant the spreadsheet itself was old (created in 2011). I was thinking it may have been a compatibility issue in the file itself. No importing was happening.

 

[Dave Patton]

Thanks for the feedback.