Formula for retirement date

[minnie]

I need a formula for the following:

Cell E45 = 8/9/2002 (retire date)
Cell E46 = 6/1/1943 (birth date)

In cell E47, I will be subtracting the two dates to get the age in years and months.

In cell E48, I will be subtracting the two dates to get the age in months.

I need it to compute as follows: if cell E45 is <=28 (28th day of month), then I need it to compute effective the following month -- in this case, 9/1/2002. If cell E45 is >=28, then compute on that date.

Thanks in advance for your help.

 

[Asala42]

G'day,

Cell E45 = 8/9/2002 (retire date)
Cell E46 = 6/1/1943 (birth date)

In cell E47, I will be subtracting the two dates to get the age in years and months.

In cell E48, I will be subtracting the two dates to get the age in months.

I need it to compute as follows: if cell E45 is <=28 (28th day of month), then I need it to compute effective the following month -- in this case, 9/1/2002. If cell E45 is >=28, then compute on that date.

I believe you want to use the DATEDIF function - but wrapped with an IF-statement to cover the months ending in days after the 28th.

=IF(DAY(E45)<=28,DATEDIF(E46,E45,"m"),DATEDIF(E46,E45,"m")+1)

or the same formula re-written:

=DATEDIF(E46,E45,"m")+(IF(DAY(E45)>28,1,0)

for the number of months difference

Hope that helps,
Adam