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.
Nathan,
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.
Has anyone figured this out? I’m having the same issue - I really need the formula column to calculate total hours worked. The hours input are typically at night, going past midnight - which breaks the formula. Anyone have a solution?
Hi David,
Here is one way of doing it:
Convert the CALL time to a date: For instance, take today’s date at 0:00 and use the ADD_MINUTES function to add your CALL column.
Let’s call it Syntax1:
ADD_MINUTES(FORMAT_DATE(TODAY(),"YYYY-MM-DD"), LEFT({CALL},2) * 60 + RIGHT({CALL},2))
Do the same for END, but add 1 day to TODAY() so that it is tomorrow.
Let’s call it Syntax2:
ADD_MINUTES(FORMAT_DATE(ADD_DAYS(TODAY(),1),"YYYY-MM-DD"), LEFT({END},2) * 60 + RIGHT({END},2))
So we now have 2 dates that we can compare: if I take the first line of your screenshot, they would be today at 10PM and tomorrow at 2:30AM. To get the number of hours between the 2, simple use the DAYS() function:
DAYS(<ReplaceWithSyntax1>,<ReplaceWithSyntax2>)*24
Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.
Check our blog for real use cases.
Thank you so much Gilles! This was incredibly helpful!!
To handle cases where the End Time is after midnight, you can add 24 hours to the End Time if it’s earlier than the Start Time. Here’s how you can adjust your formula:
IF({End Time} < {Start Time}, HOURS_DIFF({End Time} + 24, {Start Time}), HOURS_DIFF({End Time}, {Start Time}))
This formula checks if the End Time is before the Start Time. If it is, it adds 24 hours to the End Time to account for the shift crossing midnight, then calculates the difference. Otherwise, it simply calculates the difference between the two times.
@GCavin your formula works great -IF the end time is after midnight - if end time is before midnight it thinks it’s the next day and calculates it wrong, any solution?
Hi David,
Assuming that the difference between {Start} and {End} is less than 24 hours, then you want to test if {CALL}<={END}.
If {CALL}<={END}, we are in the case scenario when {END} is before midnight. Then Syntax2 is the same as Syntax1, except you replace {CALL} by {END}.
There may be a simpler way when {END} is before midnight, but at least this way will work.
Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*
Hi @GCavin yeah unfortunately that won’t work for any end time that occurs after midnight…that only works for hours before midnight…I need it to be able to calculate hours whether they’re before or after midnight. I switched the hours columns to 24H/military time and it still won’t work…
Publish your syntax.
Okeedoke! In this case - (btw this works fine if end time is before midnight - but not for after midnight.)
Syntax 1 - Column name: DONT TOUCH CALL: ADD_MINUTES(FORMAT_DATE(TODAY(),“YYYY-MM-DD”),LEFT({CALL},2)*60 +RIGHT({CALL},2))
Syntax 2 - Column name: DONT TOUCH END: ADD_MINUTES(FORMAT_DATE(TODAY(),“YYYY-MM-DD”),LEFT({END},2)*60 +RIGHT({END},2))
Syntax 3 - Total Hours Formula Column: DAYS({DONT TOUCH END},{DONT TOUCH CALL})*24
I’d like to see your real syntax with the IF statements.
I don’t have the IF statements in there - the formula failed whenever/wherever I tried to insert those…
We’ve found the winning formula for this everyone, thanks to monday.com for helping out:
IF(HOURS_DIFF({End},{CALL})<“0”,ROUND(SUM(SUM(MINUS(23,LEFT({CALL},2)),DIVIDE(MINUS(60,RIGHT({CALL},2)),60)),SUM(LEFT({End},2),DIVIDE(RIGHT({End},2),60))),2),ROUND(SUM(LEFT(HOURS_DIFF({End},{CALL}),2),DIVIDE(RIGHT(HOURS_DIFF({End},{CALL}),2),60)),2))
FYI: “CALL” is the shift start rime, “End” is the shift end time.