0

What I am attempting to do

My workers submit their clock in and out times trough a google form, I have it set up to then automatically add up the duration and show it next to the work ID and name. Up until now its been working perfectly but then I discovered that some accidentally submitted there same shift 2 times. I would like an easy way to flag this overlap. I have attempted to implement this answer but it does not work for AM times. Is there any way I can also detect this for people who work through the night into the morning?

Sheet setup Current sheet

Joris 1
  • 1
  • 1

1 Answers1

1

You have to add one when the finish time is less than the start time:

=if(query(arrayformula({value(F13:F45+G13:G45),value(F13:F45+H13:H45+(H13:H45<G13:G45))}),
"select count(Col1) where
      Col1<"&value(H13+F13-1/10^4+(H13<G13))&"
  and Col2>"&value(G13+F13+1/10^4)&" label count(Col1) '' ",0)>1,"overlap","ok")

(have changed one row to create an overlap)

enter image description here

If you were just looking for exact duplicates of the shift times, you might have been able to do it more easily with Unique() though.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37