If you type a date with a two digit year, Excel treats anything up to 29 as being 2029. Once you get to 30, though, they assume you mean 1930. With 2030 only eight years away, it might be time to adjust this.
Microsoft tries to document how, but the Help topic does not match the Windows 10 Control Panel. This video shows you how to find the settings.
Fixing the 2029 Rule in Excel.
For a Mac, it is Excel / Preferences / Edit, then follow the screenshot here:
Microsoft tries to document how, but the Help topic does not match the Windows 10 Control Panel. This video shows you how to find the settings.
Fixing the 2029 Rule in Excel.
For a Mac, it is Excel / Preferences / Edit, then follow the screenshot here:
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2458.
Changing Excel's 2029 rule. Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Today, a note from John G. who was very frustrated.
He retires on January 1st, 2030 and every time he tries to enter a date like 1/1/30 with a 2 digit year, it's giving him a date in 1930.
This is known as the 2029 rule, and when they set this up, you know back when we were all worried about Y2K, it was probably a fine rule.
It was 30 years away, but we're getting closer now.
Would be nice to be able to bump that date like if we entered 29 or 30 or 31 then it would be 2030 or 2031.
Microsoft documents on how to fix this, but it is a horrible help article.
It's taken me three days to figure out what they're talking about.
Start, Settings, Control Panel, Regional Settings Icon.
Now I'm back in Windows 10.
I don't have that, so start click the little gear wheel here. That gets me in.
There's nothing here that says Regional Settings.
There's this that says Time and Language, Region right? Then I click on Region.
Then I come over here at Additional Date Time and Regional settings.
Change Date, Time or Number Formats.
Nothing here. Additional settings - nothing here.
Oh, there's tabs across the top - Date. That's where it is, right?
So right here I've got mine up to 2049.
That'll give you another 20 years, so anything from 50 to 99 will be 1900s. Anything from one or zero up to 49 will be 2049.
You can adjust this however you would like.
So if you're worried about this 2029 rule, the setting is there. In Windows 10, it's kind of hard to find.
That's the steps. Check out my new courses on Retrieve.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank John for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
Changing Excel's 2029 rule. Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Today, a note from John G. who was very frustrated.
He retires on January 1st, 2030 and every time he tries to enter a date like 1/1/30 with a 2 digit year, it's giving him a date in 1930.
This is known as the 2029 rule, and when they set this up, you know back when we were all worried about Y2K, it was probably a fine rule.
It was 30 years away, but we're getting closer now.
Would be nice to be able to bump that date like if we entered 29 or 30 or 31 then it would be 2030 or 2031.
Microsoft documents on how to fix this, but it is a horrible help article.
It's taken me three days to figure out what they're talking about.
Start, Settings, Control Panel, Regional Settings Icon.
Now I'm back in Windows 10.
I don't have that, so start click the little gear wheel here. That gets me in.
There's nothing here that says Regional Settings.
There's this that says Time and Language, Region right? Then I click on Region.
Then I come over here at Additional Date Time and Regional settings.
Change Date, Time or Number Formats.
Nothing here. Additional settings - nothing here.
Oh, there's tabs across the top - Date. That's where it is, right?
So right here I've got mine up to 2049.
That'll give you another 20 years, so anything from 50 to 99 will be 1900s. Anything from one or zero up to 49 will be 2049.
You can adjust this however you would like.
So if you're worried about this 2029 rule, the setting is there. In Windows 10, it's kind of hard to find.
That's the steps. Check out my new courses on Retrieve.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank John for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you, Nancy.
