Date conversion issue with year

**maacmaac** · 06-12-2009, 01:21 PM

I have the following value in cell K2:
CIT BANK 2.8 060135

I am using following formula to extract date:

Please Login or Register  to view this content.

The formula is treating the result as the year 1935. Desired result is for year 2035. Is there a way to "trick" excel into using year 2000?

**DonkeyOte** · 06-12-2009, 02:10 PM

A few ways, using your TEXT approach:

=0+TEXT(RIGHT(SUBSTITUTE(H2,RIGHT(H2,2),"20"&RIGHT(H2,2)),8),"00\/00\/0000")

**shg** · 06-12-2009, 02:11 PM

Or =TEXT(RIGHT(A1,6),"00\/00\/""20""00") + 0

**DonkeyOte** · 06-12-2009, 02:12 PM

Much better...

**maacmaac** · 06-12-2009, 03:00 PM

Both solutions are working. Thanks.

**martindwilson** · 06-12-2009, 04:17 PM

just for interest how accurate is
=TEXT(RIGHT(A2,6),"00\/00\/00") + 36525

**maacmaac** · 06-12-2009, 05:38 PM

I used it in spreadsheet and it appears to be adding 100 years to the original date. Not sure if that was what you were looking for.

Re: Date conversion issue with year