Forum Discussion
Excel overrules Windows regional setting for two digit year translation
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
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.
16 Replies
From Chat GPT:
Excel has its own way of interpreting two-digit years. By default, Excel uses a cutoff year of 2029. This means that two-digit years from 00 to 29 are interpreted as 2000 to 2029, and two-digit years from 30 to 99 are interpreted as 1930 to 19991.To change this behavior, you can adjust the settings in the Windows Control Panel:Open the Control Panel.Click on "Clock and Region" and then "Region."Go to the "Formats" tab and click on "Additional settings..."In the "Customize Format" window, go to the "Date" tab. Change the "When a two-digit year is entered, interpret as a year between" setting to your desired range.This should help Excel interpret the two-digit year 31 as 2031 instead of 1931Thank you, but this is NOT the answer.
a) the fact that ChatGPT refers to 'Clock and Region', while in Windows 11 there is only Region is already interesting.
b) In my original question, I already indicated that the current Windows setting is 1950-2049. So '30 should be translated into 2030. The problem is that Excel doesn't follow Window's own logic, even though the settings are that it should.
So if you take the time responding, then please read the question carefully and check if your answer works. Because I doubt if you try it on your own machine, it would. Quoting AI without fact checking is not the way to go.Don’t doubt people who try to be helpful!
The reason for posting was that I had the problem and I solved it, by using the tip from Chat GPT. It was convenient then to copy the tip from Chat GPT.
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. 🙂
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.
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.
