Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
I need to find out the amount of time between two dates for filling out
funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Yes:
See: http://www.cpearson.com/excel/datedif.htm -- Gary''s Student "Rameris" wrote: I need to find out the amount of time between two dates for filling out funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Well I guess I am the thickest individual in the solar system but doesn't
that tell me either how many years OR months OR days? I need to know how may of each for example in "Gary''s Student" wrote: Yes: See: http://www.cpearson.com/excel/datedif.htm -- Gary''s Student "Rameris" wrote: I need to find out the amount of time between two dates for filling out funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
The formula on the "Calculating A Person's Age" section of the
web page will return a string stating how many years, months, and days a person has been alive. =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days" where A1 is birthdate and B1 is death date. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Rameris" wrote in message ... Well I guess I am the thickest individual in the solar system but doesn't that tell me either how many years OR months OR days? I need to know how may of each for example in "Gary''s Student" wrote: Yes: See: http://www.cpearson.com/excel/datedif.htm -- Gary''s Student "Rameris" wrote: I need to find out the amount of time between two dates for filling out funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
If you could see me I'd be bowing to you in homage..
Thank-you very much "Chip Pearson" wrote: The formula on the "Calculating A Person's Age" section of the web page will return a string stating how many years, months, and days a person has been alive. =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days" where A1 is birthdate and B1 is death date. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Rameris" wrote in message ... Well I guess I am the thickest individual in the solar system but doesn't that tell me either how many years OR months OR days? I need to know how may of each for example in "Gary''s Student" wrote: Yes: See: http://www.cpearson.com/excel/datedif.htm -- Gary''s Student "Rameris" wrote: I need to find out the amount of time between two dates for filling out funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
I hope you don't mind answering another question but I used the same basic
formula with two different dates. The results were correct as I now have XX Years XX months and XX days for a calculation of a person's age YY years YY months and YY days for a calculation of a person's time with the company When I tried to add those two numbers by simply putting for example @sum(d4+d5) I got a # VALUE error The reason I wanted to add those two was a person can retire from our company when a combination of their age and years of service hit 80 combined total. Can you explain what I'm doing wrong in that case? "Chip Pearson" wrote: The formula on the "Calculating A Person's Age" section of the web page will return a string stating how many years, months, and days a person has been alive. =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days" where A1 is birthdate and B1 is death date. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Rameris" wrote in message ... Well I guess I am the thickest individual in the solar system but doesn't that tell me either how many years OR months OR days? I need to know how may of each for example in "Gary''s Student" wrote: Yes: See: http://www.cpearson.com/excel/datedif.htm -- Gary''s Student "Rameris" wrote: I need to find out the amount of time between two dates for filling out funeral benefits. The form asks how long the person has been alive in Years Months and Days. I would like to know if I put for instance 10/21/1955 in say A1 as the birth date and 01/25/2006 in B1 as the date of death. So what is the formula, if one, to calculate the time in years months and days that has passed between the two dates? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
You are trying to add two strings of text, so you get the #VALUE error.
Pete |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Thanks Pete.. at lease I know why.. but do you know how to correct the problem?
"Pete" wrote: You are trying to add two strings of text, so you get the #VALUE error. Pete |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Assuming you have two connected dates in A1 and B1 (eg date of birth
and today( ) ), and two more dates in A3 and B3, the following formula in, say, B6 will give you almost what you want: =(DATEDIF(A1,B1,"y")+DATEDIF(A3,B3,"y")) & " years, " & (DATEDIF(A1,B1,"ym")+DATEDIF(A3,B3,"ym")) & " months, " & (DATEDIF(A1,B1,"md")+DATEDIF(A3,B3,"md")) & " days" - all one formula. Unfortunately, this can give return values like; 65 years, 14 months, 23 days and I'm not sure how to correct the number of months (I've never used DATEDIF before!) Here's an alternative, using the same initial dates where dates in B are later than those in A: =TEXT((B1+B3-A1-A3)/365.25,0)&" years, "&TEXT((MOD((B1+B3-A1-A3),365.25)),0)&" days" and this returns values like: 66 years, 82 days although if you use 365 days in the formula instead of 365.25 with the same dates that I did then it would show 66 years, 98 days Take your pick, and I hope you can make some use of them. Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
If your date of birth is in A2 and hire date is in B2 this should give the combined age and length of service as of today =DATEDIF(A2+B2,NOW()*2,"y")&" years, "&DATEDIF(A2+B2,NOW()*2,"ym")&" months, "&DATEDIF(A2+B2,NOW()*2,"md")&" days" Of course if this were to give a result of, say, 70 years then there would only be 5 years until retirement because each year that passes counts twice, once towards age and once on length of service. The calculation is necessarily an approximation because of variable month lengths. As you can appreciate, that DATEDIF construction is great for showing ages etc. but not fantastic for calculations. You could calculate an employees date of retirement based on your criteria using this formula. =B2+(A2-B2+365.25*80)/2 format as date Again this is an approximation based on 365¼ days in a year.... -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505391 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Thank you Pete .. it worked perfectly and I even understand it.
"Pete" wrote: Assuming you have two connected dates in A1 and B1 (eg date of birth and today( ) ), and two more dates in A3 and B3, the following formula in, say, B6 will give you almost what you want: =(DATEDIF(A1,B1,"y")+DATEDIF(A3,B3,"y")) & " years, " & (DATEDIF(A1,B1,"ym")+DATEDIF(A3,B3,"ym")) & " months, " & (DATEDIF(A1,B1,"md")+DATEDIF(A3,B3,"md")) & " days" - all one formula. Unfortunately, this can give return values like; 65 years, 14 months, 23 days and I'm not sure how to correct the number of months (I've never used DATEDIF before!) Here's an alternative, using the same initial dates where dates in B are later than those in A: =TEXT((B1+B3-A1-A3)/365.25,0)&" years, "&TEXT((MOD((B1+B3-A1-A3),365.25)),0)&" days" and this returns values like: 66 years, 82 days although if you use 365 days in the formula instead of 365.25 with the same dates that I did then it would show 66 years, 98 days Take your pick, and I hope you can make some use of them. Pete |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation between two dates
Okay, cheers.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
calculation sundays between two dates | Excel Discussion (Misc queries) | |||
calculation of interest between two dates | Excel Worksheet Functions | |||
Calculation to determine days between two dates | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |