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

  • Thread starter Thread starter Dan Cotts
  • Start date Start date
D

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.
 
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
 
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.
Click to expand...

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
 
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?
 
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?
 
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?
 
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?
Click to expand...

The parentheses () immediately after B3.
--ron
 
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)
 
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?
 
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!!!
 
Great! I hope that is what you need!

Dan said:
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!!!

:
Click to expand...
 
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?
 
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?
 
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.
 
Gotcha - Thanks!

Bob I said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Year of Birth, from DOB, in later calculations 5
Calculate age 3
date 2
balance date 3
using a year in a date 7
Expanding Date formula 1
age calculations 2
Access 2 Digit year in Access Text Box 3

Back
Top