 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Totaling DATEDIF SUMS
I have two (A&B) columns with dates in each I them have (C) with the formula =DATEDIF(A6,B6,"y") & " years, " & DATEDIF(A6,B6,"ym") & " months, " & DATEDIF(A6,B6,"md") & " days" I need to total column (C) OR I have also revised it so that (C) is =B1-A1 resulting in a number string and then formatting the cell to read yy " years, " mm " months, " dd " days" but I still can't get a total of all these years............... Hello kathi, using =B1-A1 and formatting as yy " years, " mm " months, " dd " days" won't give you the correct result, e.g. if B1-A1 =40 days that will (incorrectly) give a result of 2 months and 9 days. I'd stick with DATEDIF, then for a sum, assuming you have dates in rows 1 to 10, try =DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"y")&" years, "&DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"ym")&" months, "&DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"md") & " days" > I have two (A&B) columns with dates in each > I them have (C) with the formula =DATEDIF(A6,B6,"y") & " years, " & [quoted text clipped - 4 lines] > and then formatting the cell to read yy " years, " mm " months, " dd " days" > but I still can't get a total of all these years............... And the OP should be made aware that DATEDIF has a minor problem when the start date is the end of January and the end date is the first of March. For example, using the OP's formula... =DATEDIF(A6,B6,"y") & " years, " & DATEDIF(A6,B6,"ym") & " months, " & DATEDIF(A6,B6,"md") & " days" she should try these combinations... A6: 1/31/2007 or 1/30/2007 B6: 3/1/2007 or A6: 1/31/2008 B6: 3/31/2008 Rick > Hello kathi, > [quoted text clipped - 22 lines] >> days" >> but I still can't get a total of all these years............... That's a very good point, Rick. This formula will give you very similar results to the 3xDATEDIF formula, but without any negative numbers =YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years "&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months "&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))&" days" > And the OP should be made aware that DATEDIF has a minor problem when the > start date is the end of January and the end date is the first of March. For [quoted text clipped - 41 lines] > >> days" > >> but I still can't get a total of all these years............... If you put the three DATEDIF functions in separate columns, one for year, month and day (without the text labels), then you will find it easier to total them, with appropriate carry forwards. If you use =B1-A1 in C1 this will give you the number of days difference, so you can total these easily and then split into years, months and days. You might like to keep column C like this (you can hide the column) and then format column D to give the display how you have it now in C. Hope this helps. Pete > I have two (A&B) columns with dates in each > I them have (C) with the formula =DATEDIF(A6,B6,"y") & " years, " & [quoted text clipped - 4 lines] > and then formatting the cell to read yy " years, " mm " months, " dd " days" > but I still can't get a total of all these years............... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.