y2k problem

[kshelton]

So I'm importing a file from a query and many of the dates are coming up
incorrectly. Some years are showing up as 2018 instead of 1918, 2024
instead of 1924. I think you get the picture. I found the below on MS Excel
Help, but I would like to know if there is any way to work around it or
change the setting. Thanks in advance for any assistance!

How Excel interprets two-digit years

To ensure that year values are interpreted as you intended, type year values
as four digits (2001, rather than 01). By entering four digits for the years,
Excel won't interpret the century for you.

For Microsoft Windows 2000 or later

If you are using Microsoft Windows 2000 or later, the Regional Options in
Windows Control Panel controls how Excel interprets two-digit years.

For dates entered as text values

When you enter a date as a text value, Excel interprets the year as follows:

00 through 29 Excel interprets the two-digit year values 00 through 29 as
the years 2000 through 2029. For example, if you type the date 5/28/19, Excel
assumes the date is May 28, 2019.
30 through 99 Excel interprets the two-digit year values 30 through 99 as
the years 1930 through 1999. For example, if you type the date 5/28/98, Excel
assumes the date is May 28, 1998.

 

[Niek Otten]

From your own post:

<you are using Microsoft Windows 2000 or later, the Regional Options in Windows Control Panel controls how Excel interprets
two-digit years.>

What is your question?

Or did I miss anything?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| So I'm importing a file from a query and many of the dates are coming up
| incorrectly. Some years are showing up as 2018 instead of 1918, 2024
| instead of 1924. I think you get the picture. I found the below on MS Excel
| Help, but I would like to know if there is any way to work around it or
| change the setting. Thanks in advance for any assistance!
|
| How Excel interprets two-digit years
|
| To ensure that year values are interpreted as you intended, type year values
| as four digits (2001, rather than 01). By entering four digits for the years,
| Excel won't interpret the century for you.
|
| For Microsoft Windows 2000 or later
|
| If you are using Microsoft Windows 2000 or later, the Regional Options in
| Windows Control Panel controls how Excel interprets two-digit years.
|
| For dates entered as text values
|
| When you enter a date as a text value, Excel interprets the year as follows:
|
| 00 through 29 Excel interprets the two-digit year values 00 through 29 as
| the years 2000 through 2029. For example, if you type the date 5/28/19, Excel
| assumes the date is May 28, 2019.
| 30 through 99 Excel interprets the two-digit year values 30 through 99 as
| the years 1930 through 1999. For example, if you type the date 5/28/98, Excel
| assumes the date is May 28, 1998.

 

[Pete_UK]

You can change the settings in Windows Xp by clicking Start | Control
Panel | Date, Time, Language, and Regional Options | Regional and
Language Options | Regional Options (tab) then click Customise and
select the Date tab. Here you can change from the 30/70 year split - I
think you are likely to need something like 10/90.

Alternatively, ensure that you have the century years in your dates.

Hope this helps.

Pete

 

[kshelton]

Thanks for your reply, Niek. I'm sorry, I don't see the setting in the
Regional Settings to choose in order to change it from recognizing 1918 as
2018. Forgive me for being dense. Perhaps a little clarification?

 

[Elkar]

Since this is a "Y2K" problem, is it safe to assume that all of the dates
you're working with are from 1900 to 1999? If so, then you could use this
formula to convert the dates:

=IF(YEAR(A1)>=2000,DATE(YEAR(A1)-100,MONTH(A1),DAY(A1)),A1)

HTH,
Elkar

 

[kshelton]

I have made this change in Regional Settings, even rebooted afterwards,
checked to make sure the change stuck...with no change in Excel's response.
I'm still getting dates in the future. Any other suggestions?

 

[Niek Otten]

It think it depends a bit on your operating system. But it can be done, in any case. Just be patient.
I have Windows XP
and there it goes like this from the desktop:

Start>Control Panel>Date, Time, Language and Regional Options>Change the format of numbers, dates and times>Customize>Date
tab,>When a two-digit year is entered, interpret it as....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks for your reply, Niek. I'm sorry, I don't see the setting in the
| Regional Settings to choose in order to change it from recognizing 1918 as
| 2018. Forgive me for being dense. Perhaps a little clarification?
|
| "Niek Otten" wrote:
|
| > From your own post:
| >
| > <you are using Microsoft Windows 2000 or later, the Regional Options in Windows Control Panel controls how Excel interprets
| > two-digit years.>
| >
| > What is your question?
| >
| > Or did I miss anything?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | So I'm importing a file from a query and many of the dates are coming up
| > | incorrectly. Some years are showing up as 2018 instead of 1918, 2024
| > | instead of 1924. I think you get the picture. I found the below on MS Excel
| > | Help, but I would like to know if there is any way to work around it or
| > | change the setting. Thanks in advance for any assistance!
| > |
| > | How Excel interprets two-digit years
| > |
| > | To ensure that year values are interpreted as you intended, type year values
| > | as four digits (2001, rather than 01). By entering four digits for the years,
| > | Excel won't interpret the century for you.
| > |
| > | For Microsoft Windows 2000 or later
| > |
| > | If you are using Microsoft Windows 2000 or later, the Regional Options in
| > | Windows Control Panel controls how Excel interprets two-digit years.
| > |
| > | For dates entered as text values
| > |
| > | When you enter a date as a text value, Excel interprets the year as follows:
| > |
| > | 00 through 29 Excel interprets the two-digit year values 00 through 29 as
| > | the years 2000 through 2029. For example, if you type the date 5/28/19, Excel
| > | assumes the date is May 28, 2019.
| > | 30 through 99 Excel interprets the two-digit year values 30 through 99 as
| > | the years 1930 through 1999. For example, if you type the date 5/28/98, Excel
| > | assumes the date is May 28, 1998.
| >
| >
| >

 

[Gord Dibben]

Windows Regional settings allows only a 100 year span for interpretation of
years entered as 2 digit.

You have to choose which 100 year span to employ.

Use the spin button to change that span.


Gord Dibben MS Excel MVP

 

[kshelton]

Thanks for your response. Actually, I did this, and it's still not affecting
Excel...