SOLVED

Excel overrules Windows regional setting for two digit year translation

Copper Contributor

It is documented that as a rule, Excel will translate two digit dates either to the 1900's or the 2000' based on the breaking point 2029. So '30 will become 1930.

With that year only 8 years in the future, it would seem that MS should reconsider this standard.

 

It is also documented that the Windows Regional setting might overrule this. However, this is not the case. At least not for me. My setting (Control Panel > Region > Additional settings > Date > When a two-digit year is entered, interpret it as a year between) is set to 1950 and 2049. However, from 1/1/30 onward, the year will be 1930. 

 

Strangely I cannot find any reference to this issue. I have supplied feedback to MS, but sharing it here  in case I overlooked something in advanced Excel's options. Excel for Microsoft 365 MSO (16.0.14326.20702) 32-bit

12 Replies

@Kipopstok 

The 2029 Rule

If you want to type a date that is before January 1, 1930, or after December 31, 2029, you must type the full four-digit year. For example, to use the date July 4, 2076, type 7/4/2076.

 

Dates in the inclusive range from January 1, 1900 (1/1/1900) to December 31, 9999 (12/31/9999) are valid.

 

I don't know if this information will help you in any way, but I've included it as a notice. :)

 

NikolinoDE

I know I don't know anything (Socrates)

Thank you but no, the information is not helpful. The issue was specified as Excel overruling the Windows setting, with the undesired result. Typing a 4-digit date is a work-around, not a solution.

@Kipopstok 

The only thing I can suggest is to pre-convert the cells to text and use error checking to check for the century if necessary.

Or type an apostrophe (‘) before a number, example: '01.02.30.

The apostrophe does not appear in the cell after pressing Enter.

 

 

Thanks, but as said: I'm NOT looking for a workaround.
My question was rather specific if there was any Windows option I overlooked that is making Excel ignore the Windows setting. Because according to MS's own information Excel should follow Windows on this.

And anyway: adding the apostrophe is as much of a hassle as typing a 4-digit date. And by rendering the input to text you can't use formulas calculating the no. of days between two dates. And this is exactly the reason why the century actually IS relevant in the first place.

So thanks, but unless you have an answer to my question: "Is there a Windows option I overlooked that is making Excel ignore the Windows setting (contrary to MS's own design)", pls ignore this thread.

I came looking for the same issue and you have identified an issue that Microsoft seem to have overlooked.

On Windows 11 using Microsoft 365, I found that the custom 2-digit date conversion for the Calendar has been moved forward to interpret the year as between 1950 and 2049. Having been born in 1952, I think that Microsoft have jumped a bit far in the OS, but Excel ignores this setting and, as you rightly point out, converts a year like 33 as 1933.

I suspect that Excel is not looking at this OS setting on startup.

@PhilC52 

 

I found that if you go and change the setting in the advanced tab- then scroll to When calculating the workbook- check the  Use 1904 date system. 

@Brian-amsco05 : thanks, but can you specify which setting you mean when you say: "change the setting in the advanced tab"?

@Kipopstok 

  1. Click on File
  2. Click Options (at the bottom)
  3. Click Advanced
  4. Scroll to "When calculating this workbook"
  5. check the box next to "Use 1904 date system"
For me - W11 and MS 365 for enterprise it doesn't work.
I clicked File > Optons > Advanced > When calculating this workbook and checked the Use 1904 date system box. Still short date 20/4/30 became 20 april 1930 i/o 2030.
Tried shutting down the workbook and then trying again (checked the box was still ticked), but no luck.
best response confirmed by Kipopstok (Copper Contributor)
Solution

@Kipopstok 

Default setting (1950-2049) in Windows 11 doesn't work from the box, we need first to "activate" it. Change on 1951-2050 or whatever you prefer. Apply. After that you may return back to 1950-2049 and apply again. It shall work now, 30 goes to 2030. Not necessary to restart Excel after the setting is applied.

Hi Sergei, thanks. Incidentally I had already fiddled with the Windows setting just now. But I never thought to check back in Excel. You are right: in Windows 11 Excel does follow the Windows setting - if you 'activate it' as you suggested. If you do that, you don't need to check the 'Use 1904 date system' in the Excel sheet.
This doesn't solve the problem in Windows 10, but that problem will run out by end of 2025 anyway...

@Kipopstok , you are welcome.

Yes, Windows 10 lives with that issue.

1 best response

Accepted Solutions
best response confirmed by Kipopstok (Copper Contributor)
Solution

@Kipopstok 

Default setting (1950-2049) in Windows 11 doesn't work from the box, we need first to "activate" it. Change on 1951-2050 or whatever you prefer. Apply. After that you may return back to 1950-2049 and apply again. It shall work now, 30 goes to 2030. Not necessary to restart Excel after the setting is applied.

View solution in original post