Link to sheet: https://docs.google.com/spreadsheets/d/1dwpeRWkkbX0adBeQkwMUI6PQa6WqRCNzNluSOw0RU5k/edit?usp=sharing

Our business pays a different rate for drive time vs. site time. We also pay overtime (over 8 hours), and double overtime (over 12 hours). Sometimes we also have a crew going to several different job sites in a day (no more than 7). Therefore I can have several different pay categories:

  • Drive
  • Site
  • Drive (Overtime)
  • Site (Overtime)
  • Drive (Double Overtime)
  • Site (Double Overtime)

Using IF and AND in combination I can determine Site (DOT) and Drive (DOT). My issue is properly determining Drive (OT) and Site (OT). I have somewhat of a solution in mind using IFS and AND in combination but it seems cumbersome and clunky. Any advice is greatly appreciated.

I have formulas in cells on the sheet listing total hours, hours after break, total overtime, etc.

Here is what a selection of the times looks like enter image description here

1 Answer 1


If you know how to get double overtime, you should be able to get overtime with this logic:

overtime === total time8 Hours W/Breakdouble overtime

Your sample spreadsheet shows that you are using "digital hours" that look like 6.75 instead of durations like 6:45. It is usually simpler to stick to durations. See this answer for an explanation of how date and time values work in spreadsheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.