CODEWITHSUNDEEP
google-sheetstime
Joris 1
Joris 1

Reputation: 1

automatically flagging duplicate times

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

Upvotes: 0

Views: 39

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34265

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.

Upvotes: 1

Related Questions