1940 To 2040
By Richard Rost
18 hours ago
How to Adjust the Windows 2-Digit Year Cutoff Date
In this lesson, we will explain Windows' two-digit year cutoff and why dates such as 1/1/40 may be interpreted as 2040 in Microsoft Excel and Microsoft Access. You will learn where to find and change the cutoff setting in Control Panel, use a Run command shortcut to access it, and see how the change affects both programs. We will also discuss why entering four-digit ISO dates is the best way to avoid date ambiguity.
Robin from Phoenix, Arizona (a Platinum Member) asks: I work with geriatric patients, and I'm constantly entering birth dates from the 1930s and 1940s. Every time I type 1/1/35, Windows changes it to 2035. Is there a way to make it assume 1935 instead?
Members
In the extended cut, I will show you how to build a reusable Microsoft Access VBA function that interprets two-digit years using your application's own cutoff date instead of the Windows setting, and how to apply it to specific date fields with an After Update event.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Recommended Courses
Keywords
TechHelp Windows, Windows two-digit year cutoff, Excel two-digit year dates, Access date century cutoff, change Windows date cutoff, Excel 11/40 becomes 2040, Access birth date entry, Regional date settings, intl.cpl, ISO date format, two-digit year interpretation
Subscribe to 1940 To 2040
Get notifications when this page is updated
Intro In this lesson, we will explain Windows' two-digit year cutoff and why dates such as 1/1/40 may be interpreted as 2040 in Microsoft Excel and Microsoft Access. You will learn where to find and change the cutoff setting in Control Panel, use a Run command shortcut to access it, and see how the change affects both programs. We will also discuss why entering four-digit ISO dates is the best way to avoid date ambiguity.Transcript Have you ever typed a date in like 11/40 into Excel or Microsoft Access or whatever, only to watch it mysteriously turn into January 1, 2040?
If you're entering in historical dates, birth dates, or older records, that can be incredibly frustrating.
Welcome to another TechHelp video brought to you by Windows Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to talk about Windows' two-digit year cutoff and why programs like Excel and Microsoft Access sometimes choose the wrong century. But more importantly, I'll show you where this setting lives, how to change it, and why there's even a better habit you should get into that avoids the problem altogether.
Today's question comes from Robin in Phoenix, Arizona, one of my Platinum members.
Robin says, "I work with geriatric patients, and I'm constantly entering birth dates from the 1930s and 40s. Every time I type in 11/35, Windows changes it to 2035. Is there a way to make it assume 1935 instead?"
Well, yes, absolutely. But first, let's talk about why this happens.
So why does typing a date like 11/35 suddenly give you the year 2035? Well, the problem is that a two-digit year is ambiguous. The computer sees 35, but it has no way to know whether you meant 1935, 2035, or 2435 if you're in the Star Trek universe.
Windows solves that problem by using a two-digit year cutoff. Now, with the current default setting, it's 2026 right now. Windows 00 through 49 are treated as 2000 through 2049, and years 50 through 99 are treated as 1950 through 1999.
So this means that 11/35 becomes January 1st, 2035, while 11/85 becomes January 1st, 1985. So Windows has to draw the line somewhere.
But the important thing to understand is that this isn't really an Excel setting or an Access setting. Both programs are just asking Windows to interpret what you typed in. And that's why the same two-digit date behaves the same way in both applications.
Of course, the easiest way to avoid this whole problem is just to type in four digits for each year. Use the ISO date standard. ISO dates for everyone. That's my mission in life, to get the whole world to use this year, month, day. It's completely unambiguous. Go watch this video to learn more. I'll put a link down below.
Now, if you've got the kind of job where you're typing in dates from the last century and you want to avoid this problem, changing the Windows cutoff date can save you a lot of extra typing. Let's see how this works.
So right now, if I open up an application like Excel, go to a blank workbook, and I type in something like 11/40, it immediately changes it to 2040. And if I type in something like 11/72, I get 1972. An awesome year, by the way. Lots of great things happened that year.
This also happens the same way in Microsoft Access. If I come in here, Customer Sense, type in 11/40, I get 2040. If I type in 11/80, I get 1980.
It's not an application setting. It's a Windows setting.
Now, how do we find that, and where do we change it in Windows? Well, if you're a normal person, just click on Start and then type in Control Panel. C-O-N-T-R. There it is, Control Panel. Open that up.
And if you haven't yet already, if you're going to use this a lot, you could pin it down here on the taskbar by right-clicking and going to Pin. I've got it pinned right now, so it'll say Unpin.
Once you've got it here, this is the Control Panel. Go to Clock and Region, and then under Region, you've got this thing that comes up. Go to Additional Settings down here. Lots of settings in here. Go to Date, and right here is your cutoff date.
So if you routinely work with geriatric patients, you can back this up to, like, 2029. So now, if you type in 30, you're going to get 1930. Just hit Apply.
This is what it used to be a few years back, by the way. I did a video, man, about 15 years ago, one of my Access videos, I think, at one of the expert levels, where the cutoff date at that time was 2029. But now they've moved up, of course. And depending on when you're watching this video, it might be different for you.
But now, hit OK. Hit OK again. You can close the Control Panel.
Oh, and by the way, if you're a nerd like me and you hate clicking through different dialog boxes like that, if you've got to go around to, like, 15 different machines and change the same setting, here's a shortcut. Press Windows-R on your keyboard. That'll bring up the Run dialog. Type in I-N-T-L dot C-P-L. Just remember it. You'll have to do this once every 10 years.
Hit Enter, and that'll bring you right there. Then you just go to Additional Settings, Date, and there it is.
Now, once you've done that, you can go back into Excel. And I'll type in 11/30 this time, and there we go. I got 1930. 11/70, 1970. Beautiful.
Same thing with Access. 11/40 gives me 1940. Notice, I didn't change any settings in Excel or Access. I only changed Windows. Both programs immediately follow the new Windows setting because that's where they get their two-digit-year interpretation from.
But of course, the best strategy, in my opinion, is this guy right here. He's the ISO date format, and type in four-digit years. That's the best thing to do. Then you don't have to mess with any settings.
Now, should you change your system? And you might be wondering why Microsoft picked 2049 in the first place. Are they watching Blade Runner? I know that was a different one. But anyways, it makes a lot of sense if you think about how most people use a computer.
Most data entry involves today's businesses. Invoices, appointments, due dates, warranties, schedules, future events. If someone types in 35, they probably mean 2035, not 1935. So Windows is optimized for the way the average person uses a computer.
Now, if your job is entering birth dates in a doctor's office and you work for a genealogist, then absolutely you want to switch it. Or if you're working at a museum or a historical society or managing a cemetery database. You're dealing with dates from the last century all the time.
So in those situations, changing the Windows cutoff can save you a lot of extra typing and frustration. For everyone else, the default setting is probably just fine.
And regardless of which cutoff you choose, my recommendation is still the same: whenever possible, type in the full four-digit year. That removes all ambiguity and makes your data crystal clear, no matter what computer you're using. And if you use the ISO dates, it doesn't matter what country you're in.
Now, for those of you who don't know me, if you just stumbled upon this Windows video and it's your first time here, welcome. Most of what I do is teach Microsoft Access, which is Microsoft's database program that's included with many versions of Microsoft Office.
Now, as an extended cut for my members, I'm going to show you how to build a reusable PERS short date function that lets your Access application decide how to interpret two-digit years instead of relying on the Windows setting.
That way, you don't have to change everyone's computer individually. Maybe your users don't have permission to change their Windows settings, or maybe the IT department has locked those options down, or maybe you don't want to run around to 50 different workstations to change the Control Panel settings. You can just do it now in your Access application.
So with this approach, your application stays in control. You can define whatever two-digit-year cutoff makes sense for your business, and you can put it in certain fields. Maybe you only want to have this in the customer's date of birth field, but for an order date field, you don't want it doing that. So there's customization there, too.
You can also write one reusable function, and then you can hook it into any date field with a single line of code right there in the After Update property. And that's it.
And if you're an Access developer, I think you'll find this to be a very handy technique. And if you're not an Access developer and you're wondering what it is, it's an awesome database program. Check it out. Here's my full four-hour course. It's absolutely free. It'll teach you everything you need to know to get started with Access. So go check that out.
So today's takeaway is simple. When you enter a two-digit year, programs like Excel and Access aren't guessing on their own. They're using the two-digit-year cutoff that's built into Windows. So if your dates aren't coming out the way you expect, now you know where that behavior comes from and how to change it.
Or better yet, whenever possible, get in the habit of entering four-digit years. It's a simple change that eliminates the ambiguity forever.
If you found this video helpful, give me a thumbs up. Make sure you subscribe to my channel and post a comment down below. Let me know what you thought of today's video and if you plan on changing your cutoff date.
But that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Thanks for watching.
If you want me to post more videos about Microsoft Windows, then be sure to like this video, subscribe to my channel, and post a comment down below. Let me know that you want more Windows videos.
About 90% of what I teach is Microsoft Access database design, but I love teaching Windows, Word, Excel, PowerPoint, and all those other topics, too. But of course, the squeaky wheel gets the grease. So if you want more Windows training, make some noise.
You can watch my entire Microsoft Windows Beginner Level 1 course absolutely free on my website and on my YouTube channel. It's over an hour long and covers all the basics.
If you like Level 1 and want to learn more about Windows, visit my website at the link shown, and you can get Level 2, which is another complete hour-long course for just one dollar. Level 2 goes into a lot more depth and teaches you how to get the most out of Windows. Visit my website today for more information.Quiz Q1. Why can a two-digit year such as "35" be interpreted incorrectly by a program?
A. A two-digit year is ambiguous and could belong to more than one century
B. Excel only accepts dates after the year 2000
C. Access stores all dates as text
D. Windows cannot process dates before 1950
Q2. According to the default cutoff described in the video, how is the two-digit year "35" interpreted?
A. 1935
B. 2035
C. 2135
D. 1835
Q3. According to the default cutoff described in the video, how is the two-digit year "85" interpreted?
A. 2085
B. 1985
C. 1885
D. 2185
Q4. Where do Excel and Access get their interpretation of two-digit years?
A. From the Windows two-digit year cutoff setting
B. From the current workbook or database file
C. From the user's Microsoft account
D. From the keyboard regional layout
Q5. What is the best general habit for avoiding ambiguity when entering dates?
A. Type only the month and year
B. Use a two-digit year and adjust Windows later
C. Enter a full four-digit year
D. Store every date as plain text
Q6. Which date format does the instructor recommend as an unambiguous standard?
A. Day/month/two-digit year
B. Month/day/four-digit year
C. Year/month/day
D. Two-digit year/month/day
Q7. Which Control Panel area is used to change the Windows two-digit year cutoff?
A. System and Security
B. Clock and Region, Region, Additional Settings, Date
C. Programs and Features
D. Network and Internet
Q8. What shortcut command can be entered in the Run dialog to open the Region settings more quickly?
A. date.cpl
B. control dates
C. intl.cpl
D. region.exe
Q9. If the cutoff is changed to 2029, how would a two-digit year of "30" be interpreted?
A. 1930
B. 2030
C. 2130
D. 1830
Q10. Why might a medical office, genealogist, museum, or cemetery database want to change the cutoff setting?
A. They frequently enter dates from the previous century
B. They need all dates converted to text
C. They only work with future appointments
D. They need to prevent users from entering dates
Q11. What is one advantage of handling two-digit year interpretation inside an Access application instead of changing Windows on each computer?
A. The application can use its own business-specific cutoff rules
B. Access will no longer store dates
C. Excel settings will automatically change
D. Users will no longer need date fields
Q12. In an Access application, why might a developer apply custom two-digit-year handling only to certain fields?
A. Different types of dates may need different interpretation rules
B. Access only allows one date field per form
C. Order dates cannot contain years
D. Windows settings cannot affect date fields
Answers: 1-A; 2-B; 3-B; 4-A; 5-C; 6-C; 7-B; 8-C; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Windows Learning Zone explains how Windows interprets two-digit years and why dates entered in programs such as Microsoft Excel and Microsoft Access can sometimes end up in the wrong century.
If you enter a date like 11/40, you may expect it to mean January 1, 1940. Instead, Windows may interpret it as January 1, 2040. This can be especially frustrating if you work with birth dates, historical records, genealogical data, cemetery records, medical records, or any other information involving dates from the early or middle part of the twentieth century.
The problem is that a two-digit year is inherently ambiguous. When you type 35, the computer cannot know whether you mean 1935, 2035, or some other century. Windows resolves this ambiguity by using a setting called the two-digit year cutoff.
With the current default cutoff, two-digit years from 00 through 49 are treated as years from 2000 through 2049. Two-digit years from 50 through 99 are treated as years from 1950 through 1999. As a result, entering 11/35 is interpreted as January 1, 2035, while entering 11/85 is interpreted as January 1, 1985.
Windows has to draw the line somewhere, and its default setting is intended to work well for most modern business situations. Most people entering dates are working with invoices, appointments, due dates, warranties, schedules, and future events. If someone enters 35 in that situation, they will probably mean 2035 rather than 1935.
The important point is that this usually is not an Excel setting or an Access setting. Those programs generally rely on Windows to determine how a two-digit year should be interpreted. That is why the same date can be interpreted the same way in both Excel and Access.
For example, if Windows is configured to treat 00 through 49 as 2000 through 2049, entering 11/40 in Excel will result in 2040. Entering 11/72 will result in 1972. Microsoft Access will follow the same rule. A date such as 11/40 will become 2040, while 11/80 will become 1980.
The best long-term solution is to avoid two-digit years whenever possible. I strongly recommend entering a full four-digit year. Better still, use the ISO date format: year, month, day. A date such as 1935-11-01 is clear and unambiguous. It does not depend on regional settings, country-specific date formats, or Windows cutoff rules.
However, if you regularly enter older dates, changing the Windows cutoff setting can save time and reduce errors. For example, if you work with geriatric patients and frequently enter dates from the 1930s and 1940s, you may want Windows to interpret 30 as 1930 rather than 2030.
To change this setting, open the Windows Control Panel. Go to Clock and Region, then open Region. From there, open Additional Settings, select the Date tab, and locate the setting for the two-digit year cutoff.
You can set the cutoff year based on the needs of your work. For example, setting the cutoff to 2029 means that years 00 through 29 will be treated as 2000 through 2029, while years 30 through 99 will be treated as 1930 through 1999. With that setting in place, entering 11/30 will be interpreted as 1930 rather than 2030.
The default cutoff has changed over time. Years ago, 2029 was commonly used as the cutoff. Depending on the version of Windows you use and when you are reading this, your default setting may be different.
If you need to change this option on multiple computers, there is a faster method than navigating through the Control Panel each time. Press Windows-R to open the Run dialog, then enter intl.cpl. This opens the Region settings directly. From there, open Additional Settings, choose the Date tab, and adjust the two-digit year cutoff.
After changing the Windows setting, Excel and Access should immediately use the new interpretation. For example, if your cutoff is configured so that 30 through 99 represent years in the 1900s, entering 11/30 in Excel will result in 1930. In Access, entering 11/40 will result in 1940. No changes are required inside either application because both programs use the Windows setting.
Whether you should change the cutoff depends on the kind of data you enter. If you mostly work with current and future business dates, the default setting is probably appropriate. If you frequently work with historical records, birth dates, or dates involving older people, adjusting the cutoff can make data entry much easier.
Even if you change the setting, I still recommend using four-digit years whenever possible. Four-digit years eliminate ambiguity and ensure that your data is interpreted correctly on any computer, regardless of its Windows settings.
Also, in today's Extended Cut, we will look at how to create a reusable Access function that allows your database application to interpret two-digit years according to your own business rules instead of relying on the Windows setting. This is useful when users cannot change their Windows settings, when IT policies prevent system changes, or when you need different date interpretation rules for different fields. For example, you may want a special rule for a Date of Birth field while leaving order dates and appointment dates under the normal Windows behavior. By using a reusable function and connecting it to the appropriate form fields, your Access application can remain in control of how short dates are handled.
The main lesson is simple: Excel and Access do not normally make their own decisions about two-digit years. They follow the two-digit year cutoff configured in Windows. If dates are being assigned to the wrong century, check the Windows Region settings. Better yet, use complete four-digit years and ISO date formatting whenever you can.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Windows two-digit year cutoff behavior
How two-digit dates are assigned a century
Two-digit date examples in Excel and Access
Finding the Region settings in Control Panel
Changing the two-digit year cutoff date
Opening Region settings with intl.cpl
How Excel and Access use the Windows cutoff
When to change the Windows date cutoff
Using four-digit years to avoid ambiguity
Using ISO date format for unambiguous datesArticle When you enter a date with only a two-digit year, such as 11/40, Windows and many Windows applications must decide which century you mean. The year 40 could mean 1940, 2040, or another century entirely. Since the computer cannot know your intent, it uses a two-digit year cutoff rule.
This is why entering 11/40 in Microsoft Excel or Microsoft Access may become January 1, 2040, while entering 11/80 may become January 1, 1980. With a typical current cutoff, two-digit years from 00 through 49 are interpreted as 2000 through 2049. Years from 50 through 99 are interpreted as 1950 through 1999.
This behavior is especially inconvenient when you work with historical records, patient birth dates, genealogy, cemetery records, or any other data where dates from the early or middle part of the previous century are common. If you enter 11/35 intending January 1, 1935, but the system changes it to January 1, 2035, the result can cause serious data-entry errors.
The important point is that this is generally not an Excel or Access setting. These applications commonly rely on the Windows regional date settings to interpret ambiguous two-digit years. Changing the Windows cutoff can therefore affect how dates are interpreted in both Excel and Access.
To change the cutoff in Windows, open Control Panel and go to Clock and Region, then Region. Select Additional Settings, open the Date tab, and locate the setting for the two-digit year cutoff. This setting defines the final year of the current century range.
For example, if the cutoff is set to 2049, years 00 through 49 are treated as 2000 through 2049, while years 50 through 99 are treated as 1950 through 1999. If you change the cutoff to 2029, then years 00 through 29 are treated as 2000 through 2029, and years 30 through 99 are treated as 1930 through 1999. With that setting, entering 11/30 would be interpreted as January 1, 1930.
A quicker way to open the same Region settings is to press Windows-R, type intl.cpl, and press Enter. From there, select Additional Settings, choose the Date tab, and adjust the two-digit year cutoff.
Changing this setting can be useful if the computers in your workplace are primarily used for entering older dates. A medical office that regularly enters birth dates, a historical society, a museum, or a genealogy organization may benefit from using a cutoff that treats years such as 30, 35, and 40 as dates in the previous century.
However, changing the Windows setting affects how dates are interpreted across the computer. For general business use, the default cutoff is often reasonable because people frequently enter dates involving future appointments, warranties, deadlines, schedules, and other current-century events. Someone entering a year such as 35 may very well mean 2035.
The most reliable solution is not to depend on a cutoff at all. Whenever possible, enter a full four-digit year. Instead of entering 11/35, enter 1935-01-01 or 1/1/1935. A date written with a four-digit year is unambiguous and will not be affected by the Windows two-digit-year cutoff.
Using the ISO-style date format, year-month-day, is particularly helpful. For example, 1935-01-01 is clear regardless of regional date conventions. It avoids confusion between month/day/year and day/month/year formats as well as confusion about the century.
If you develop an Access application, you can also handle this within the application rather than requiring every user to change Windows settings. A reusable date-handling routine can examine a two-digit year entered in a specific field, apply a cutoff that makes sense for that field, and convert it to the intended full date. For example, a Date of Birth field might interpret 35 as 1935, while an order date field might interpret 35 as 2035. This approach lets the database enforce business-specific rules without changing settings for the entire computer.
In all cases, the safest habit is to enter complete dates with four-digit years. Adjusting the Windows cutoff can make older-date entry more convenient, but full years eliminate ambiguity entirely. |
