change 2000 to 19??

  • Thread starter Thread starter Gaye
  • Start date Start date
G

Gaye

dates have been entered as 2 figures eg 26 instead of
1926, excel converts to 2026. How can I change all 2000
dates to 19nn.
 
How about a helper cell with the following formula, and copied down.........

=A1-(100*365.25)

Vaya con Dios,
Chuck, CABGx3
 
Hi Gaye!

Use a helper column to convert:

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

I've assumed that you have the standard double digit year
interpretation boundary of 29 established in your Regional settings.
This is a Windows OS setting that treats double digits of 00 to 29 as
being this Century and 30 to 99 as being last Century.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thax lots, just too easy
-----Original Message-----
How about a helper cell with the following formula, and copied down.........

=A1-(100*365.25)

Vaya con Dios,
Chuck, CABGx3






.
Click to expand...
 
Back
Top