Calculating gaps in time in Excel

[fly4awhtguy]

I am tracking hourly productivity with my employees and looking for a way to track gaps in their electronic movements that are greater than 10 minutes. Is there a formula that I can use to track this throughout a 12 hour shift?

[FDibbins]

little difficult without a sample workbook to work from, but, assuming your time is actuallt TIME and not just a value, maybe try something like...

=if(time-you-want-to-check>time(0,10,0),"whatever-you-need-to-have-shown","")

if this isnt what you need, please upload a sample

[fly4awhtguy]

ok, thanks for the tip. I have uploaded a sample with a discription of what I am looking to do. sample for forum.xlsx

[FDibbins]

in C6, try this formula....

=IF(AND(B6=B5,A6-A5>TIME(0,10,0)),A6-A5,"")

let me knlw if this works for you?

[fly4awhtguy]

ok, I got that to work for 2 of the cells. Is there any way that I can make it calculate for the entire shift, or do I have to manually search for the gap and enter it for each individaul occurrance?

[shg]

In C6 and copy down,

=IF((A6-A5)*(B6=B5) > --"0:10:0", A6-A5, "")

Format the result as h:mm:ss or as you prefer.

[fly4awhtguy]

PERFECT! Thank you sir.

[FDibbins]

ok i just tried shg's formula, and it gives the exact same results as my suggestion did?

[shg]

Apologies to FDibbins, our formulas are the same.

[FDibbins]

just another example of (pretty much) getting the same results from (pretty much) the same approach/formula