how do I calculate a difference in dates in years and months?

[Dan Cotts]

In the Help menu, I can see how to do it in years, and I can see how to do it
in months, but how do I do YY, MM? - I am trying to calculate retirment ages.

 

[Sandy Mann]

Use the largely undocumanted DATEDIF() function. Chip has a good
explanation here:

http://www.cpearson.com/excel/datedif.htm

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sheeloo]

See http://www.cpearson.com/excel/datedif.aspx for details...

and why you don't see in Help (from the above link) - "DATEDIF is treated as
the drunk cousin of the Formula family. Excel knows it lives a happy and
useful life, but will not speak of it in polite conversation."

=DATEDIF(Date1,Date2,"m") will give complete calendar months between the dates
=DATEDIF(Date1,Date2,"y") will give complete calendar years between the dates
Date1 is the first date,
Date2 is the second date

 

[Bob I]

Format the cell as Custom YY-MM

 

[Ron Rosenfeld]

On Tue, 28 Oct 2008 10:01:02 -0700, Dan Cotts <Dan

In the Help menu, I can see how to do it in years, and I can see how to do it
in months, but how do I do YY, MM? - I am trying to calculate retirment ages.

There is a basic problem in what you are trying to do, in that neither years,
nor months, are constant intervals. A year can be 365 or 366 days. And a
month can be 28,29,30 or 31 days.

There is a built-in function, DATEDIF, documented only in Excel 2000 or at
http://www.cpearson.com/excel/datedif.aspx, which will compute the differences.
But it has certain limitations when dealing with months that have different
lengths.

If that doesn't do what you require, or if its limitations are a problem, post
back with more specifics.
--ron

 

[Dan Cotts]

thanks - but I am getting #REF! error when I run the DATEDIF logic. What I
have is

=DATEDIF(B2,B3(),"y")&" years "&DATEDIF(B2,B3(),"ym")&" months "

where b2 is the date of birth, and b3 is the date of retirement. What do I
have wrong?

 

[Dan Cotts]

thanks - but I am getting #REF! error when I run the DATEDIF logic. What I
have is

=DATEDIF(B2,B3(),"y")&" years "&DATEDIF(B2,B3(),"ym")&" months "

where b2 is the date of birth, and b3 is the date of retirement. What do I
have wrong?

 

[Dan Cotts]

Thanks - I've got these formatted in YY MM, and actually get an accurate
result when I subtract DOB from Date of Retirement. Now, I need to be able
to determine Full Retirement Age, based on Year of Birth, from the Social
Security Tables, and when I format the DOB into YYYY, to get the year of
birth, I cannot use that figure in other calculations, because it has been
converted to the Excel serial number.

Any thoughts?

 

[David Biddulph]

You don't want B3(). The cell reference is B3, without the parentheses.

 

[Ron Rosenfeld]

thanks - but I am getting #REF! error when I run the DATEDIF logic. What I
have is

=DATEDIF(B2,B3(),"y")&" years "&DATEDIF(B2,B3(),"ym")&" months "

where b2 is the date of birth, and b3 is the date of retirement. What do I
have wrong?

The parentheses () immediately after B3.
--ron

 

[Bob I]

Dates are always a serial number in Excel. Perhaps start over in your
posting here and state line by line what you are actually trying to do.
Your subject says one thing and the question seems to say another and
then you say you formatted the Date of birth to YYYY, that would be Year
of birth not Date of Birth, so use a different cell for YOB if thats
what you want.

for instance

a1 = DOB= 1/1/1945
b1 = DOR= 10/12/2009
c1 = Difference in Months and Year= b1-a1 (format cell as YY-MM)
d1= YOB= a1 (format as YYYY)

 

[Dan Cotts]

Thanks. What I am trying to do is have a user enter their date of birth,
then take the year of birth to enable me to apply the Social Security Tables,
to figure out what the Full Retirement age is, based on the Year of birth.

What I have thus far is very similar to what you have below:
b2: DOB = 07/16/41
B3: DOR = 05/15/08
b30: b2-b3 = 66 years, 10 months (custom formatted into YY, MM)
b31: YOB = 1941 (format the DOB in YYYY)

But I cannot figure out how to then use the year of birth in a formula to
determine Full Retirement Age (if 1937 or before, 65 years; if 1938, 65
years, 2 months; 1939, 65 years 4 months, etc). In my formula, I have
=if(b31<=1937, "65 years",if(b31=1938, "65 years, 2 Months" - etc.

But the value that is in b31 in the serial number (17153, or something, so
my formula is never true - regardless of what year. Do I need to determine
the individual serial numbers for 1/1/xxxx for each year, and use those in my
formula?

 

[Dan Cotts]

I think I may have found it! I asked the same question in a different post,
and 3 people suggested use =Year(b2) - and that is returning a 1941 number -
that does not appear to be in serial number form!!!

 

[Bob I]

Great! I hope that is what you need!

I think I may have found it! I asked the same question in a different post,
and 3 people suggested use =Year(b2) - and that is returning a 1941 number -
that does not appear to be in serial number form!!!

:

 

[Dan Cotts]

hmmmm - okay, it worked for most everything, but the Social Security Charts
have a range in the middle - if you were born btwn 1943 and 1954, your full
retirement age is 66. This is my formula, and it returns a proper value for
everything except when the Year of Birth is in that range - then it returns
false:

=IF(B28<=1937,"65 Years",IF(B28=1938,"65 years, 2 months",IF(B28=1939,"65
years, 4 months",IF(B28=1940,"65 years, 6 months",IF(B28=1941,"65 Years, 8
months",IF(B28=1942,"65 years, 10 months",IF(1943<=B28<=1954,"66
years",IF(B28=1955,"66 Years, 2 months",0))))))))

where B28 is the year of birth, using =year(b2), which is the date of birth.

what am I missing?

 

[Bob I]

change
1943<=B28<=1954
to
(1943<=B28)*(B28<=1954)

 

[Dan Cotts]

Thanks! I have no idea what you did, but it worked!

 

[Dan Cotts]

ok, I am really not very good at this....

I need to calculate that date of full retirement - is there any way to add
the Full Retirement Age that I just calculated to the date of birth to get
this?

 

[Bob I]

I'm not familiar with the term you are using. Are you wanting to add X
months and Y years to their Date of Birth to get a date sometime in the
future? The "If" statements are not a calculation but merely text
returned for visual purposes. You will need to work with real numbers.
One way is to have two cells, one returning years, and one returning
months. You can use the If statement in both just leave in the numbers.
example for months would be

=IF(B28<=1937,0,IF(B28=1938,2,IF(B28=1939,4,IF(B28=1940,6,IF(B28=1941,8,IF(B28=1942,10,IF((1943<=B28)*(B28<=1954,0,IF(B28=1955,2,0))))))))


Do the same for the year, stripping out the text, then you have real
numbers to work with that you can add to the Month year part of the
Birthday.

 

[Dan Cotts]

Gotcha - Thanks!

I'm not familiar with the term you are using. Are you wanting to add X
months and Y years to their Date of Birth to get a date sometime in the
future? The "If" statements are not a calculation but merely text
returned for visual purposes. You will need to work with real numbers.
One way is to have two cells, one returning years, and one returning
months. You can use the If statement in both just leave in the numbers.
example for months would be

=IF(B28<=1937,0,IF(B28=1938,2,IF(B28=1939,4,IF(B28=1940,6,IF(B28=1941,8,IF(B28=1942,10,IF((1943<=B28)*(B28<=1954,0,IF(B28=1955,2,0))))))))


Do the same for the year, stripping out the text, then you have real
numbers to work with that you can add to the Month year part of the
Birthday.