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

IF(
  AND({Start-End the same?} = 1, {Diff hours} > 4), 
  'Alert',
  IF(
    AND({Start-End the same?} = 0, {Diff hours} > 19),
    'Alert'
  )
)

Thanks so much for the quick response!

I plan to use the intake date that a new file comes into my table via an online form at the start time. Is there a way to incorporate that as the start of the clock and then have the stop time be when the team member moves the file to a different view or team
member?

Troy Gamble - NMLS #40183

Producing Branch Manager

Fairway Independent Mortgage Corp.

Desk: 425-215-4949

Just thinking…you’re going to get a weird result on a job that starts at 16:00 on a Friday and ends at 10:00 on the next Monday, assuming you’re not working over the weekend. Let me think on this for a bit…

JB

Ok, thank you….really appreciate your help on this.

Troy

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

Here is my work hour calculator with brake hours for a shift. It takes out weekends and your entered national holidays and counts from date to date entered how many work hours -lunch brake hours are in the period.
If someone is interested and need help on this please write.

Hi there

I’m not able to see the calculator or calculation in your email below? Very interested though so thank you for sending.

Troy

Hey everyone, I just wanna say that I love this community, always helpful and supportive🙏

I would like to share the below function, it might help out with a similar use case:

The above function does a simple task, it calculates the number of hours using the DATETIME_DIFF function for all days within the range, then it takes out the number of weekend hours; by subtracting the number of workdays for the same time range from all days within that time range, then you're left out with just the business hours between these two dates, in my case, to calculate the SLA for a task.

Hope this helps in any way!