I'm having an issue with a spreadsheet I am working on,
The spreadsheet subtracts a start time from an end time and flags the difference if it's equal to or greater than 6 minutes and 1 second. Using the sumifs function it adds all the times together that are over the threshold and allows me to put an x in the column next to values to negate a value if I want to subtract it from the total. It works as intended for every time I have tested except for 18:00-18:06:01, 05:00-05:06:01, and 17:00:00-17:06:01. However if I change the second from 1 to a 2 it works
1: The total time should be 0:24:06
The formulas I have are
=SUMIFS(C2:C26,D2:D26,"<>x",C2:C26,">=0:06:01")
and
=(IF(AND(A2>=TIMEVALUE("19:58"),A2<=TIMEVALUE("20:05")),B2-A2-$G$3,IF(AND(A2<=TIMEVALUE("22:13"),A2>=TIMEVALUE("22:20")),B2-A2-$G$4,IF(AND(A2>=TIMEVALUE("01:43"),A2<=TIMEVALUE("01:50")),B2-A2-$G$3,B2-A2))))
Any suggestions on what I am missing/not understanding correctly would be greatly appreciated. Thank you
Excel is evaluating "00:06:01" as a slightly smaller value than 05:06:01 - 05:00:00.
One solution is to use the Excel value for 00:06:01, which is 0.00417824074074074 and drop some non-significant trailing digits, for example: 0.00417824074. Then change your formula to just:
=SUMIFS(C2:C26,D2:D26,"<>x",C2:C26,">0.00417824074")
That is, drop the "equal to" test and test it against your slightly smaller value.
