Number of months between dates

[minnie]

Scenario: someone retires at age 51. There's a 5% penalty between ages 50 - 55 and a 2.5% penalty between ages 55-60.

Cell E44 11/24/1949 (birthdate)
Cell E45 11/1/2001 (retire date)
Cell E47 623 (age at retire in months)
(Formula =DATEDIF(E44,E45,"M")
Cell E52 97 total number of months penalized

In cell E48 I need a formula to calculate # of months penalized for ages 50-55 (11/1/2001-11/24/2004) and in cell E49 I need a formula to calculate # of months penalized for ages 55-60.

The total number of months penalized is 97, but part of it needs to go to the 50 to 55 category and part of it needs to go to the 55 to 60 category.

Any help would be appreciated.

[Jay Petrulis]

Hi,

Try the following, assuming E44 is the date of birth and E45 is the retirement date as in your sample (note the negative signs carefully):

For the number of months penalized for ages (50,55]
=-MIN(DATEDIF(E44,E45,"M")-660,0)

For the months penalized for ages (55,60)
=-MIN(DATEDIF(A1,A2,"M")-720,0)+MIN(DATEDIF(A1,A2,"M")-660,0)

You could also incorporate the penalty amount directly.

Multiply the above formulas by
(1-penalty%)*(monthly payment)

HTH,
Jay

[dimrod]

If someone was born on 31 Oct 1949 and retired on 30 Nov 2001, the formula =DATEDIF(E44,E45,"m") produces an age at retirement of 624 months.
Should this not be 625 months?