HELP!!!! Date Calculations

  • Thread starter Thread starter Teri
  • Start date Start date
T

Teri

Hello,

I posted this question a few weeks back and the answer I
received, while greatly appreciated, did not work for me
so I thought I'd try once more.

I have created a spreadsheet to show two dates (date of
birth, retirement date). I need to calculate how old the
person is on their date of retirement (in years and
months). So what I've done is created two columns, one
for years and one for months (e.g. the person is 59 years
and 8 months old).

Here is where it gets complicated. If the person is born
on the 14th of the month of before, then that month is NOT
counted in their age. If they are born on the 15th of the
month of later, then that month IS counted in their age.

Any help that could be provided would be GREATLY
appreciated.

Thanks very much,

Teri
 
Teri,

Please see J.E. McGimpsey's Post in excel.programming.
He has pointed out that your crediting someone younger
(born after the 15th) as being older (born before the 15th)

As per his realization he has posted solutions, mine would
be ammended to

To get the years
=DATEDIF(A1,B1,"Y")
To get the months
=DATEDIF(DATE(YEAR(A1),IF(DAY(A1)<=15,MONTH(A1),MONTH(A1)+1),DAY(A1)),B1,"YM")

Dan E
 
Hi again Dan,

I've just realized that the formula doesn't work! I don't
know what I'm doing wrong. I've entered a birthdate of
Jan. 8/46 and a retirement date of Feb. 1/04. But the
answer the formula gives me is that the person is 58 years
and 11 months old, when in actuality they are 58 years and
1 month old on their retirement date.

If their birthdate is Jan. 16/46 and retirement date is
still Feb. 4/04, then their age at retirement is 58 years
old exactly.

I know it seems illogical to have someone who is born
later in the month older than someone born earlier in the
month, but that is the way that pensions are calculated.

If you could help me, I would really appreciate it.

Thanks,

Teri
 
Teri,

For Years
=DATEDIF(DATE(YEAR(A1),IF(DAY(A1)>=15,MONTH(A1)-1,MONTH(A1)),DAY(A1)),B1,"Y")
For Months
=DATEDIF(DATE(YEAR(A1),IF(DAY(A1)>=15,MONTH(A1)-1,MONTH(A1)),DAY(A1)),B1,"YM")

For your example (BD = Jan. 8/46, RD = Feb. 1/04)
Returns 58 years and 0 months
For your example (BD = Jan. 16/46, RD = Feb. 1/04)
Returns 58 years and 1 months

Dan E
 
For Years
=DATEDIF(DATE(YEAR(A1),IF(DAY(A1)>=15,MONTH(A1),MONTH(A1)+1),DAY(A1)),B1,"Y")
For Months
=DATEDIF(DATE(YEAR(A1),IF(DAY(A1)>=15,MONTH(A1),MONTH(A1)+1),DAY(A1)),B1,"YM")

For your example (BD = Jan. 8/46, RD = Feb. 1/04)
Returns 57 years and 11 months
For your example (BD = Jan. 16/46, RD = Feb. 1/04)
Returns 58 years and 0 months

This formula assumes the retiree recieves no credit for
a partially worked month

*Can't believe I'm helping rip off retirees.

Dan E
 
Back
Top