Re: Calculate "business hours" formula help
Hi @Troy_Gamble - there is a way to do this:
Start and End are just regular date/time fields
Diff Hours is:
DATETIME_DIFF(End, Start, 'hours') - note that the result is in whole hours only, see below.
Start Date is the date portion of “Start”, using:
DATETIME_FORMAT(Start, 'DD/MM/YYYY')
End Date is the same idea on “End”
“Start-End the same?” checks to see if the two date portions are the same day or not:
IF({Start Date} = {End Date}, TRUE(), FALSE())
The Alert field is:
IF(
AND({Start-End the same?} = 1, {Diff hours} > 4),
'Alert',
IF(
AND({Start-End the same?} = 0, {Diff hours} > 19),
'Alert'
)
)
So what this is saying is:
- If the two date portions are the same AND the number of hours difference/worked > 4, then alert
- Else if the two date portions are different, then alert if the hours difference > 19 (the 4 hours you want the task to take plus 15 hours off work time between 5pm and 8am
By implication, Same Day and <= 4 hours is good (no alert) and Different Day and <= 19 hours is good. If a task takes from “day 1” to “day 3” or “day x” then this is covered too.
WHOLE HOURS - DATETIME_DIFF only calculates in whole hours, so if your team are booking time in partial hours, e.g. 09:00 to 13:30, and you want to alert this as over 4 hours, then the set-up above won’t work. You would need to change the DATETIME_DIFF to work on minutes instead of days and modify the downstream formulas appropriately.
Obviously, hide any calculated fields you don’t want to see.
Hope this helps
JB
Hi @Troy_Gamble - so the weekend thing just requires another field to check if the start day is a Friday and then use this in the alert formula:
Weekday is:
WEEKDAY({Start Date})
and now Alert is:
IF(
AND({Dates same?} = 1, {Diff Hours} > 4),
'Alert',
IF(
AND({Dates same?} = 0, Weekday != 5, {Diff Hours} > 19),
'Alert',
IF(
AND({Dates same?} = 0, Weekday = 5, {Diff Hours} > 67),
'Alert'
)
)
)
I think all of this assumes tasks start during working hours Mon-Fri. If a task is logged on, say, a Saturday, it won’t work.
If you want the start time to be the time the task was logged via a form, you can use the record “created at” time (formula field using CREATED_TIME()). If you use this, then, as above, the “whole hours” thing is going to come into play, so you’ll need to work the difference between the start and end in minutes and adjust this all the way through.
If you want the closing of the task to be based on a status change, then you can do this too:
Above, I’ve used a single select for the status field (which might contain many different statuses). “Status Last modified” is a field of type “last modified time” and the completed time is:
IF(Status = 'Completed', {Status Last Modified})
You have to do this in two steps as you don’t want to know the last modified time for all statuses, just for “completed”. So, set your “start” field to the record created at time and your end field to the “completed time” and it should work for you.
JB
