Calculate "business hours" formula help

Hi there

I would like to be able to know how many hours a tasks takes a member on my team so I can be alerted if over 4 hours. The catch is that I want to calculate only business day hours not just straight hours. Example would be if the tasks is started at 4 pm, and the WorkDay ends at 5, then only one hour. Then the clock would start again at 8 AM. until the 4 hours accumulated.

I’m thinking it’s not possible but wanted to see if anyone in the community had any ideas. Thanks!

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

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