Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A mathematical formula question???
I have put the following formula in a cell which gives me the result
following. The question I have is if there are multiple rows is there a way that I can add the results give to come up with the total number of years months days? What this is for is the people that have retired. The result of 39 years etc. is how much time they have with the company. I'd like the spreadsheet to also total the grand total amount of years months days for an announcement at the retirement function. =DATEDIF(B2,C2,"y") & " years, " & DATEDIF(B2,C2,"ym") & " months, " & DATEDIF(B2,C2,"md") & " days" 39 years, 1 months, 30 days |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A mathematical formula question???
I think that you will find that if you sum the years, months and days then
the result will never be totally accurate because you would need to take into account the month of the year each of the individuals commenced and finished so that you know how many days the months represent because of the differing number of days in the months. Of course for the individual the result you have is accurate but it will not be accurate when you start to sum them. I think that you will be better to simply create a column where you find the Datedif in days (or simply subtract the start date from finish date) for the individuals and format the cell to numeric. You can then sum the numeric days and divide by 365.25 and I am sure that the result in years and decimals will suffice for your needs. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A mathematical formula question???
=DATEDIF(SUM(B2:B999),SUM(C2:C999),"y") & " years, " &
DATEDIF(SUM(B2:B999),SUM(C2:C999),"ym") & " months, " & DATEDIF(SUM(B2:B999),SUM(C2:C999),"md") & " days" -- David Biddulph Rameris wrote: I have put the following formula in a cell which gives me the result following. The question I have is if there are multiple rows is there a way that I can add the results give to come up with the total number of years months days? What this is for is the people that have retired. The result of 39 years etc. is how much time they have with the company. I'd like the spreadsheet to also total the grand total amount of years months days for an announcement at the retirement function. =DATEDIF(B2,C2,"y") & " years, " & DATEDIF(B2,C2,"ym") & " months, " & DATEDIF(B2,C2,"md") & " days" 39 years, 1 months, 30 days |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A mathematical formula question???
Thank you David. It appears to have carried forward the same calculation for
all. I makes sense to do it as a group and not total the columns "David Biddulph" wrote: =DATEDIF(SUM(B2:B999),SUM(C2:C999),"y") & " years, " & DATEDIF(SUM(B2:B999),SUM(C2:C999),"ym") & " months, " & DATEDIF(SUM(B2:B999),SUM(C2:C999),"md") & " days" -- David Biddulph Rameris wrote: I have put the following formula in a cell which gives me the result following. The question I have is if there are multiple rows is there a way that I can add the results give to come up with the total number of years months days? What this is for is the people that have retired. The result of 39 years etc. is how much time they have with the company. I'd like the spreadsheet to also total the grand total amount of years months days for an announcement at the retirement function. =DATEDIF(B2,C2,"y") & " years, " & DATEDIF(B2,C2,"ym") & " months, " & DATEDIF(B2,C2,"md") & " days" 39 years, 1 months, 30 days |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mathematical Formula For simple mathematics¿ | Excel Discussion (Misc queries) | |||
interest rate mathematical formula | Excel Discussion (Misc queries) | |||
mathematical formula of rate | Excel Discussion (Misc queries) | |||
IS there a mathematical formula for NORMSINV? | Excel Worksheet Functions | |||
Please help with an excel mathematical formula | Excel Discussion (Misc queries) |