adding years and months

Hi Team

I need another help

In Cell “K2” I have Date(10/01/2001) and “S2” Cell “2.1” (means 2 years 1 month)
Now want the total in “T2” adding the years and months

Final output in T2 is = 11/01/2003 ( adding one month + adding 2 years )

Hope it is clear

Regards
Sagar

thanks a lot

Sagar

=K2+DATE(INT(S2),10*(S2-INT(S2)), )

Won't that give the same result for 2 yrs 1 month (2.1), and 2yrs 10 months (2.10)?

True, my fault

Originally Posted by sweep

=K2+(DATE(LEFT(S2,FIND(".",S2)-1),RIGHT(S2,LEN(S2)-FIND(".",S2)),))

This will always be approximately a month out. If S2 = 2.1 then you're doing this

=K2+DATE(2,1,0)

=DATE(2,1,0)

gives the 31st December, i.e. you are adding 2 years

Also, the formula will only distinguish between 2.1 and 2.10 if S2 is text-formatted.

So if S2 is text formatted you can use this formula

=DATE(YEAR(K2)+INT(S2+0),MONTH(K2)+MID(S2,FIND(".",S2)+1,2),DAY(K2))

but I'd suggest a numeric format for S2 with 2 years and 1 month shown as 2.01 and 2 years 10 months shown as 2.10 in which case you could use this formula:

=DATE(YEAR(K2)+INT(S2),MONTH(K2)+MOD(S2,1)*100,DAY(K2))

or using Analysis ToolPak functions

=EDATE(K2,ROUND(DOLLARDE(S2,12)*12,0))