# Calculate Total Time elapsed (Inconsistency between 2007 and 2010)

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## 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!  Register To Reply

4. ## 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.  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

7. ## 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?  Register To Reply

8. ## 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.  Register To Reply

9. ## 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  Register To Reply

10. ## Re: Calculate Total Time elapsed based on time variables Originally Posted by dip11 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.  Register To Reply

11. ## 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.  Register To Reply

12. ## 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?  Register To Reply

13. ## 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?  Register To Reply

14. ## 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.  Register To Reply

15. ## Re: Calculate Total Time elapsed based on time variables

Hm unfortunately didn't fix it  Register To Reply

16. ## 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.  Register To Reply

17. ## 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))  Register To Reply

18. ## 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  Register To Reply

19. ## 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.  Register To Reply

20. ## 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  Register To Reply

21. ## 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  Register To Reply

22. ## 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   Register To Reply

23. ## 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?  Register To Reply

24. ## 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?  Register To Reply

25. ## 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.  Register To Reply

26. ## 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?  Register To Reply

27. ## 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.  Register To Reply

28. ## 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  Register To Reply

29. ## 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.  Register To Reply

30. ## 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  Register To Reply

31. ## 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.  Register To Reply

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

Cheers, works also fine without the divisor :D  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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