21st century years

[Fred Smith]

I have a column of maturity dates which I converted to Excel dates using
DATEVALUE. The dates all had two digit years. DATEVALUE converted years
prior to 30 in the 21st century, and years greater than 30 in the 19th. But
I want them all in the 21st century. Some years go up to 51.

I figured my regional settings were the culprit, so I changed the two-digit
year option from 2030 to 2055. No success. So I closed Excel, rebooted,
loaded the file again, but still no success

Any ideas where I look next?

 

[millsy]

Assume your dates are in column A starting at A1.

In B1 enter the formula =if(A1<=11323,A1+36525,A1)

That will convert all your dates to 21st century.

Oops - forgot to mention - Copy this formula dow

 

[Gord Dibben]

Fred

I played around with this Fred. Looks like the DATEVALUE Function ignores the
Windows Settings.

Stand-alone dates entered as dates reflect the changes in Windows Settings,
but I can't get the DATEVALUE Function to read 2051, whether entered before or
after changing the settings.

From Help................

For Windows NT Workstation 4.0 or dates entered as "text values"(my quotes)

When you enter a two-digit year value in Windows NT Workstation 4.0 or 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.

.........................................

Sounds like the text-entered dates are hard-coded to 2029

BUG!!!!!!<g>

Kludgy work-around =DATEVALUE("23/1/51")+(365.25*100) returns Jan. 23, 2051

Gord Dibben Excel MVP

 

[Dave Peterson]

I'd use another helper cell:

Say A1 contains your date in text.
=datevalue(a1)
in B1, so it would contain the real date.
then finally, in C1:
=date(if(year(b1)<2000,100+year(b1),year(b1)),month(b1),day(b1))

 

[beeawwb]

You gotta admit though, it's pretty sad that you have to make formulas
that add on 100 years just to make dates come up in the 21st century.

Just a thought that popped into my head. What about VBA code that, when
you entered a date, (before or after update, not sure which) and then
checked what year it was, and changed the year for you? I don't know
specifically how to write it, but I'm sure somebody out there would.

-Bob

 

[Fred Smith]

Thanks all. It's nice to know it's not me. My wife just got a new computer
with Office 2003 on it, and I tested it there. Same results. I guess
Microsoft really thinks this is a "feature".

 

[Ron Rosenfeld]

Just a thought that popped into my head. What about VBA code that, when
you entered a date, (before or after update, not sure which) and then
checked what year it was, and changed the year for you? I don't know
specifically how to write it, but I'm sure somebody out there would.

In Windows XP, the following VBA function seems to interpret 2 digit years
according to the Windows Regional Settings:

Function DateValue21(datestring As String) As Date
DateValue21 = CDate(datestring)
End Function


--ron