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

Suggestion posted in microsoft.public.excel.misc

"Teri" wrote in message ...
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

One way:

Your explanation seems backwards to me - someone born on the 14th is
older than someone else born on the 15th, but based on your
explanation, the younger one would be credited with being a month
older...???

In any case, per your explanation:

Assume Birthdate in A1, Retirement date in B1. Then

C1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"y")
D1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"ym")

if it's the reverse of what you posted, change "14" to "<=14"



In article ,
"Teri" wrote:

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

I forgot to include my assumption that the retiree got credit for
the entire month in which he/she retires. If that's not correct,
then here's another way of interpreting it:

C1: =INT((DATEDIF(A1,B1,"m")-(DAY(A1)<14))/12)
D1: =MOD((DATEDIF(A1,B1,"m")-(DAY(A1)<14)),12)

In article
,
"J.E. McGimpsey" wrote:

One way:

Your explanation seems backwards to me - someone born on the 14th is
older than someone else born on the 15th, but based on your
explanation, the younger one would be credited with being a month
older...???

In any case, per your explanation:

Assume Birthdate in A1, Retirement date in B1. Then

C1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"y")
D1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"ym")

if it's the reverse of what you posted, change "14" to "<=14"

I followed up to this post and didn't even think of that...

Looks like I need more coffee...

Good Eye on your part though

Dan E

"J.E. McGimpsey" wrote in message ...
One way:

Your explanation seems backwards to me - someone born on the 14th is
older than someone else born on the 15th, but based on your
explanation, the younger one would be credited with being a month
older...???

In any case, per your explanation:

Assume Birthdate in A1, Retirement date in B1. Then

C1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"y")
D1: =DATEDIF(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)14), 1),B1,"ym")

if it's the reverse of what you posted, change "14" to "<=14"



In article ,
"Teri" wrote:

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