Date problem

I'm using this formula to extract a date from a serial number and add 10 years to the result.

The 2000 dates are all correct, however, all of the 1900 dates are resulting in numbers like:

19103
19106
19108

What do I need to do to correct this problem?

Here's the formula:

=IF(B5="ACME",IF(MID(D5,3,1)*1>=5,19&MID(D5,3,2)+10,20&MID(D5,3,2)+10),"")

Originally Posted by NBVC

Can you give examples of what may be in D5?

Yes, It's a number/letter combo. The 3rd and 4th digits will always be the two digit representation of a Year.

The dates can go back as far as 1950 and as current as yesterday.


4405E47911 = 2005
4405E47887 = 2005
4894E02417 = 1994
0196E11959 = 1996
2204E30812 = 2004

Originally Posted by NBVC

Try:

=IF(B5="ACME",1900+MID(D5,3,2)+IF(MID(D5,3,1)*1>=5,0,100)+10,"")

I tried it and the result was 7/7/05 for D5 with a value of: 4405E47911

It should have been 2005 with 10 years added: 2015

Your original formula works OK if you just add a few parentheses

=IF(B5="ACME",IF(MID(D5,3,1)*1>=5,(19&MID(D5,3,2))+10,(20&MID(D5,3,2))+10),"")

as NBVC says, just format as general

or, shorter..

=IF(B5="acme",(20-(MID(D5,3,1)>"4")&MID(D5,3,2))+10,"")

Originally Posted by daddylonglegs

Your original formula works OK if you just add a few parentheses

=IF(B5="ACME",IF(MID(D5,3,1)*1>=5,(19&MID(D5,3,2))+10,(20&MID(D5,3,2))+10),"")

as NBVC says, just format as general

or, shorter..

=IF(B5="acme",(20-(MID(D5,3,1)>"4")&MID(D5,3,2))+10,"")

Thanks so much you guys! It works perfectly.