+ Reply to Thread
Results 1 to 32 of 32

Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

  1. #1
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    Hi,

    I have 6 time variables from which I would like to reliably calculate the total time elapsed.
    I am not quite sure how to go about it - My current formula doesn't handle exceptions very well.
    Please Login or Register  to view this content.
    Note that there are no dates associated with the time variables, but if a time looks smaller than the time in the column to its left, assume that the date incremented by 1. (You can see how I did that in the formulae provided in the worksheet)

    To make your life easier helping me with this, I have already included the desired output which I have corrected manually.

    Note that there are a number of blank cells around, which are intentionally blank (missing data)

    Ideally no additional columns should be relied on that don't already exist due to space limitations on the original file.

    Thanks for any help you can offer.

    Use Workbook.xlsx for trying to find a working formula.

    Alternatively:

    --
    jason.b75 provided this formula but it seems to give differing results based on whether Excel 2007 or 2010 is used:

    Please Login or Register  to view this content.
    Refer to file "dip11 error test" for a small sample of the data to figure out if you can correct his formula to work with 2010.
    Attached Files Attached Files
    Last edited by dip11; 09-05-2012 at 12:15 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    I'm struggling to figure out how you're getting some results.

    For example

    Please Login or Register  to view this content.
    I would have thought the results should be 180 for row 1 and 1465 for row 2.

    Could you outline the methed you use to manually calculate your results so that it's a little easier to follow?

    Looking at the 2 examples above, should the difference between time2 and time4 be totalled as 0 or 1440?

  3. #3
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Hi,

    Thanks for giving it a try - sorry I had some mistakes in the original upload, I've fixed them now (they would have caused issues if you tried to add every field).
    So the 21:50 - 21:45 you saw is now both 21:45.

    The other thing I've fixed is I've added a comment to a select few of the entries which I had to manually adjust based on external factors. Those 4 or so rows would just need 1440 added on top and I've marked them. For all intents and purposes you can ignore these.

    Other than that, it is exactly like you said.

    The formula I used initially was essentially Time6 - Time1, but that didn't account for all sots of variation in between. I then tried to add 1440 if the Total was smaller than some of the other results, but outside of creating a checker for every pair opf columns, that wouldn't have worked out and missed a few entries.

    I suppose the straightforward method that only uses the 1 column would be to add the distance of every value on a row to get the result, but having 10+ nested IF statements did my head in and I got really confused!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    I have a formula that 'appears' to be working, I have 13 results that differ from yours, out of those, 5 are correct when you add the 1440 from the adjust column.

    The formula is slow to calculate so I'm going to see if I can improve it before posting, I also need to include the adjust values into it.

    Can the 'Adjust' column be split into 2?

    It will make things easier if the value to adjust was separated from any accompanying text.

  5. #5
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    It is OK to not consider the extra 1440 for those special cases as there's nothing in the times that would indicate they would normally be required. I will mark the cells in a different color in my workbook eventually.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    See how this looks

    Workbook.zip

    I've changed cell colour to red on the rows where the results differ from yours, 13 in total.

  7. #7
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Hi Jason,

    Thanks for the code, I notice that all the ones you marked in red are either the ones with modifiers or an entry error due to me - I've corrected the latter now.

    The code you wrote generally seems to work, but seems to stumble when it comes to times being the same. i.e. 14:30 - 14:30 increments by 1 day instead of counting it as a 0 time value.
    There's about 3000 values where your result is several days larger than the actual.

    Figure you could fix that?
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    Are the examples an exact match to your real data? Can you provide a sheet with some examples where that happens?

    The only scenario I can reproduce that would appear to add 24 hours to identical times is when the times appear identical, but the time on the right is earlier than the time on the left by less than 0.5 seconds.

  9. #9
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Yes, they are pasted directly out of my master database.

    As far as I can tell, the times are straight figures (they appear as say 13:25:00 when clicked on), so I'm not sure how I could fix the problem you mention about 0.5 second differences.
    Essentially I manually type all the times in, i.e. "14:" which Excel converts into 14:00:00 or sometimes I type "14:00" which should be the same I think.

    Since the issue also occurs with the above 13:25, which is typed exactly as it appears as 13:25, I have no idea what could cause any 0.5 second differences, in any

    The way I noticed the issue in the workbook you attached was by sorting total time descending and scrolling down to the first visible numbers
    Last edited by dip11; 09-03-2012 at 10:30 AM.

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    Quote Originally Posted by dip11 View Post
    Since the issue also occurs with the above 13:25, which is typed exactly as it appears as 13:25, I have no idea what could cause any 0.5 second differences, in any
    As you're typing them in that format, there is no way it could happen, when I suggested that as a possible cause I was thinking that your data could be system generated timestamps.

    I've tried sorting the sheet from post #7 in descending order, and the results all appear to match your expected manual entries in the adjacent column, could you provide a couple of 'ID' numbers for rows that are incorrect.

    The only places I can see an extra day being added to 2 identical times is when there is a different time between them, i.e.

    14:30 - 15:00 - 14:30 which would total as 24, 0.5 for the first time difference + 23.5 for the second.

  11. #11
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    I've taken a screenshot of what I mean.
    Only difference from the upload of yours (post #6) is that its sorted Z to A for the bold Total Time and top row is frozen.

    The upload from me in #7 is your workbook form #6, with my errors corrected and most of your code deleted (kept first 2 rows only so can be copied down) so I could fit it in the upload.
    Attached Images Attached Images
    Last edited by dip11; 09-04-2012 at 06:27 AM.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    I can't reproduce what you're getting, it could be a problem specific to your version of excel, see how this compares when you open it, when I saved the file the only differences were the 13 rows marked red from the earlier posts.

    Do you get the same variances as with your screenshot from post #11?
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Same issues - I have no idea what's going on.
    I don't think Excel2010 would cause this compared to 2007 though?
    Attached Images Attached Images

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    I think I might have found something, a missing 0 in the formula that might be defaulting to 1 in your version of excel.

    Enter this in K2 and array confirm it with Shift Ctrl Enter, then fill down.

    =IF(COUNT(B2,D2:G2)>1,ROUND(SUM(IFERROR(MOD(CHOOSE({1,2,3,4,5},INDEX(C2:G2,MATCH(TRUE,C2:G2<>"",0))-(B2&"")+1,INDEX(D2:G2,MATCH(TRUE,D2:G2<>"",0))-(C2&"")+1,INDEX(E2:G2,MATCH(TRUE,E2:G2<>"",0))-(D2&"")+1,INDEX(F2:G2,MATCH(TRUE,F2:G2<>"",0))-(E2&"")+1,(G2&"")-(F2&"")+1),1),0))*1440,),"")

    If that works then it means that IFERROR behaves differently in 2010.

  15. #15
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Hm unfortunately didn't fix it

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    I've taken a small sample from the sheet to make it easier to work with, in this test file,

    The top table is formula based, so should behave the same as the previous version, the bottom table has values entered for comparing results.

    column J shows the incorect results from your screenshot.
    column K still holds the original formula, which in theory should show the same value as column J
    Columns N:R show the individual calculations of difference between the relevant header and the next time to the right, blanks and identical times show as 0.
    Column T show the total of the individual calculations.

    Hopefully this will help us to pinpoint the cause of the error.
    Attached Files Attached Files

  17. #17
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculate Total Time elapsed based on time variables

    A little 5th quarter addition.... With this I get a match on all but a couple rows where your math looks to be wrong. Just make sure the first word in column M is "Overnight" on any rows you want to make the manual adjustment.

    =IF(COUNT(B2:C2)<2, "", ROUND(((LOOKUP(2, 1/(ISNUMBER(C2:G2)), C2:G2)-B2)+(MIN(C2:G2)<B2)+(LEFT(M2,9)="Overnight")) *1440,0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  18. #18
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Very smart way of going about it

    As you can see in my screenshot, it seems that my version has some issues with column O and M(Time2 > ?, Time3 > ?)

    ---

    Oh wow that was a simple and effective solution JBeaucaire! Thanks very much!

    Still wondering though hy the 2007 and 2010 thing differs out of curiosity.

    Repped you both for help
    Attached Images Attached Images
    Last edited by dip11; 09-04-2012 at 09:32 AM.

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    Jerry's suggestion looked a bit too simple to be completely effective, I'm not sure that it gives all results as expected, see below

    Please Login or Register  to view this content.
    I've found the cause, (floating point precision), just not quite sure how to fix it without breaking something else.

  20. #20
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Hm I didn't run it though the whole thing but you are right, seems to need a small adjustment

    14259/14495 are my errors (fixed in my previous upload) but the ones above are miscalculated

  21. #21
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Calculate Total Time elapsed based on time variables

    First, on my sheet, I get 50, not 1465, for row 14259.

    =IF(COUNT(B14260:C14260)<2, "", ROUND(((LOOKUP(2, 1/(ISNUMBER(C14260:G14260)), C14260:G14260)-B14260)+(MIN(C14260:G14260)<B14260)+(LEFT(M14260,9)="Overnight")) *1440,0))


    I'll admit, I may have missed the obvious in regards to what is expected to happen with "fuller" rows. My formula simply ignores rows with nothing in B or C. Then it takes the LAST value it finds in the row and subtracts it from the B value, then adds 1 if the last value is less than the B value, or if the word "Midnight" is in column M.



    11070 = 1775 with my formula.
    =IF(COUNT(B11071:C11071)<2, "", ROUND(((LOOKUP(2, 1/(ISNUMBER(C11071:G11071)), C11071:G11071)-B11071)+(MIN(C11071:G11071)<B11071)+(LEFT(M11071,9)="Overnight")) *1440,0))

    10741 = 1746 with my formula
    =IF(COUNT(B10732:C10732)<2, "", ROUND(((LOOKUP(2, 1/(ISNUMBER(C10732:G10732)), C10732:G10732)-B10732)+(MIN(C10732:G10732)<B10732)+(LEFT(M10732,9)="Overnight")) *1440,0))

    7535 = 1675 with my formula
    =IF(COUNT(B7536:C7536)<2, "", ROUND(((LOOKUP(2, 1/(ISNUMBER(C7536:G7536)), C7536:G7536)-B7536)+(MIN(C7536:G7536)<B7536)+(LEFT(M7536,9)="Overnight")) *1440,0))

    9970 = 1595 with my formula
    1653 = 1515 with my formula
    8232 = 1465 with my formula
    14495= 19 with my formula, not 13
    Last edited by JBeaucaire; 09-04-2012 at 03:27 PM.

  22. #22
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    I've attached the most up to date version of the workbook (please use this for testing your formulae) with JBeaucaire's formula with a check whether something is a mismatch or match, sorted by mismatch.

    It seems that there's 2 issues with the formula:
    1. It gives a total time even when one of Time1 or Time6 are blank (these are the Time In and time Out values essentially and must be valid for a total time to display)
    2. It seems to ignore if there's a smaller time in between that requires +1440 adjustment.

    Contrast with my basic formula that I used initially to get the Total Time + lots of manual adjustments:
    =IF(OR(A2="",G2=""),"",ROUND((G2-A2+(A2>G2))*1440, 0))


    It does seem that Jason's formula does what I ask for, except that it somehow doesn't work for Excel 2010 properly
    Attached Files Attached Files

  23. #23
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed based on time variables

    One mismatch, which looks like an incorrect check value, how many do you get with 2010?
    Attached Files Attached Files

  24. #24
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed based on time variables

    Yes somehow missed 9222, ugh! Definitely my fault.

    In terms of the others, 2797 mismatches due to 2010. I wish I knew what's causing it. Any idea where to ask for advice on that?

  25. #25
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    As a side thought, are you able to use vba solutions?

    Given the problems encountered, I think a udf might be a preferable option.

  26. #26
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    Yes, I'm happy with that

    I've been thinking a bit about the issue and have attached yet another screenshot. I think this one shows why in 2010 at least I get 1440 for subtimes that should be 0.

    0.666666666667-0.666666666667+1 = 1 = 1440

    So it seems that if you were to add a clause in there to only add +1 if the result of the subtraction is not 0 that should fix it?
    Attached Images Attached Images
    Last edited by dip11; 09-06-2012 at 06:02 AM.

  27. #27
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    In theory that would work, but it would mean duplicating the arrays that cause the formula to calculate slowly, I'm going to try a copule of things, but think it's going to be preferable to look at alternatives.

    The MOD function is there for that purpose, if you evaluate the formula in O2 to the same point as your screenshot, then a couple more clicks should produce a negative decimal value as a result of differences casued by floating point precision something like -3.33xxxxxxxxE-16, then you should see the formula evaluate to MOD(1,1) which would result as 0.

    Without having both versions of excel side by side to compare the results step by step it's hard to figure out which part is causing the error.

  28. #28
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    Yes, I just clicked a bit further to your point and it got to the floating decimal. However it evaluated it as 1 rather than 0
    Attached Images Attached Images

  29. #29
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    At least we have managed to isolate the cause now, although a cure may not be so simple.

    See if this works

    =IF(COUNT(B2,G2)=2,ROUND(SUM(IFERROR(MOD((1/(1/ROUND(CHOOSE({1,2,3,4,5},INDEX(C2:G2,MATCH(TRUE,C2:G2<>"",0))-B2,INDEX(D2:G2,MATCH(TRUE,D2:G2<>"",0))-(C2&""),INDEX(E2:G2,MATCH(TRUE,E2:G2<>"",0))-(D2&""),INDEX(F2:G2,MATCH(TRUE,F2:G2<>"",0))-(E2&""),G2-(F2&"")),8)))+1,1),0),--(LEFT(J2,9)="Overnight"))*1440,),"")

    The ROUND function eliminates the floating decimals, so in theory, should be sufficient, the double division is there as a failsafe.

  30. #30
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    Works like an absolute charm

    Which part is the double divisor that I could remove to test if it still works without? I tried just removing one of the 1/ and that gave me some really strange numbers
    If I were to remove the overnight portion, would it just be cutting
    ,--(LEFT(J2,9)="Overnight")
    or more than that?

    Thanks

  31. #31
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    You would need to remove both 1/ to test, 1/ROUND changes the values to the strange numbers you're seeing, 1/(1/ROUND changes them back to the original value, the only difference being any 0 values being repaced with #DIV/0 errors to prevent any subsequent calculations from returning floating deciamls.

    ,--(LEFT(J2,9)="Overnight") is all you need to cut to remove the overnight adjustments.

  32. #32
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

    Cheers, works also fine without the divisor :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1