If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#12
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#13
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#14
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#15
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#16
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#17
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#18
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#19
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
#20
|
|||
|
|||
how do I calculate a difference in dates in years and months?
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. |
Thread Tools | |
Display Modes | |
|
|