Calculating shift length when shift end time passes midnight

I’m trying to create a formula that will calculate the length of a shift when the user provides a ‘Start Time’ and ‘End Time’. These columns are currently set as ‘Hours’ columns. There is no issue when the End Time value is greater then the Start Time, however; sometimes the End Time entered is after midnight creating a formula issue. I’m looking for the portion of the formula that would replace the ??? below.

IF({End Time} < {Start Time}, ???, HOURS_DIFF({End Time}, {Start Time}))

Hey Nathan! I’d be happy to try to figure this one out with you!

Could you clarify what you’re currently using the ??? for in the formula? Sorry for my confusion; knowing what placeholder the ??? is serving will help me better understand what issue you’re encountering here and how to resolve it.

Alternatively, have you considered using the Time Tracking Column for measuring shift lengths? Let me know your thoughts on this!

The time tracking column complicates my board quite a bit. I’m hoping to use an input form to quickly add a date, a start time, end time, and I want monday to automatically generate values for overtime, shift length, and unsociable hours (hours worked after midnight).

In the formula above the portions I’ve included work correctly, the issue is I cannot get the portion that would replace the ??? to work. I’m looking for a code that would work in that portion of the formula, that would calclate the total shift length when the end time is past midnight.


How are you starting and ending the shift? There are a few ways you can calculate and setup formulas based on whats activating these values.

Thank you

You will make your life a lot easier if rather than a start hour and end hour alongside a date, if you just use two full date columns - start datetime and end datetime. Since date columns can hold times.

That is going to eliminate the whole issue.

There is no automations. This sounds like the end employees enter it arbitrarily - in other words they aren’t in a position to start and stop it on monday through an automation. Just come in and enter it in manually.