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.

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

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

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


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

"Dan Cotts" wrote:

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.

Format the cell as Custom YY-MM

Dan Cotts wrote:

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.

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

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

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?


"Ron Rosenfeld" wrote:

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

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

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?



"Sheeloo" wrote:

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

"Dan Cotts" wrote:

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.

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?

"Bob I" wrote:

Format the cell as Custom YY-MM

Dan Cotts wrote:

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.

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

"Dan Cotts" wrote in message
...
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?


"Ron Rosenfeld" wrote:

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

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

On Tue, 28 Oct 2008 14:42:04 -0700, Dan Cotts
wrote:

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

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 wrote:

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?

"Bob I" wrote:


Format the cell as Custom YY-MM

Dan Cotts wrote:


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.

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?


"Bob I" wrote:

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 wrote:

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?

"Bob I" wrote:


Format the cell as Custom YY-MM

Dan Cotts wrote:


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.

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" wrote:

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 wrote:

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?

"Bob I" wrote:


Format the cell as Custom YY-MM

Dan Cotts wrote:


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.

Great! I hope that is what you need!

Dan Cotts wrote:

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" wrote:


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 wrote:


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?

"Bob I" wrote:



Format the cell as Custom YY-MM

Dan Cotts wrote:



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.

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" wrote:

Great! I hope that is what you need!

Dan Cotts wrote:

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" wrote:


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 wrote:


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?

"Bob I" wrote:



Format the cell as Custom YY-MM

Dan Cotts wrote:



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.

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

Dan Cotts wrote:

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" wrote:


Great! I hope that is what you need!

Dan Cotts wrote:


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" wrote:



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 wrote:



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?

"Bob I" wrote:




Format the cell as Custom YY-MM

Dan Cotts wrote:




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.

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

"Bob I" wrote:

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

Dan Cotts wrote:

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" wrote:


Great! I hope that is what you need!

Dan Cotts wrote:


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" wrote:



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 wrote:



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?

"Bob I" wrote:




Format the cell as Custom YY-MM

Dan Cotts wrote:




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.

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" wrote:

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

Dan Cotts wrote:

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" wrote:


Great! I hope that is what you need!

Dan Cotts wrote:


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" wrote:



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 wrote:



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?

"Bob I" wrote:




Format the cell as Custom YY-MM

Dan Cotts wrote:




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.

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 wrote:
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" wrote:


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

Dan Cotts wrote:


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" wrote:



Great! I hope that is what you need!

Dan Cotts wrote:



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" wrote:




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 wrote:




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?

"Bob I" wrote:





Format the cell as Custom YY-MM

Dan Cotts wrote:





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.

Gotcha - Thanks!

"Bob I" wrote:

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 wrote:
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" wrote:


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

Dan Cotts wrote:


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" wrote:



Great! I hope that is what you need!

Dan Cotts wrote:



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" wrote:




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 wrote:




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?

"Bob I" wrote:





Format the cell as Custom YY-MM

Dan Cotts wrote:





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.

You're welcome, have a great day!

Dan Cotts wrote:

Gotcha - Thanks!

"Bob I" wrote:


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(B 28=1940,6,IF(B28=1941,8,IF(B28=1942,10,IF((1943<=B 28)*(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 wrote:

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" wrote:



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

Dan Cotts wrote:



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" wrote:




Great! I hope that is what you need!

Dan Cotts wrote:




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" wrote:





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 wrote:





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?

"Bob I" wrote:






Format the cell as Custom YY-MM

Dan Cotts wrote:






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.