Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Rolling total in hh:mm for entry until a condition is met, but only count business hours

Topic Labels: Formulas
Solved
Jump to Solution
223 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi Community!

I’m working on a table that allows my company to easily track and time rental property application processing times. Fields I have are listed below:

Screen Shot 2021-01-29 at 11.09.59 am

Unrelated fields are hidden. We want {Hand Over Timer} to start timing once a record gets updated to “Processing”.

We don’t want to track time over weekends. For this, we decided to implement the help of the WORKDAY_DIFF() function. The problem is, this works horribly when time is involved. I’ve had to create this monstrosity to make it accurate:

IF({Processing Activated (Automation Requirement)},
IF(
    {Application Tagged As Handed Over},
    IF(
        (WORKDAY_DIFF({Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) >= 1),
        (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) * 24) * 3600)) - 172800,
        (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) * 24) * 3600)) - 86400
    ) + 
    ((HOUR({Application Tagged As Handed Over}) + (MINUTE({Application Tagged As Handed Over}) / 60)) * 3600) + 
    (86400 - ((HOUR({Application Started Processing}) + (MINUTE({Application Started Processing}) / 60)) * 3600)),
    IF(
        (WORKDAY_DIFF({Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) >= 1),
        (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) * 24) * 3600)) - 172800,
        (((WORKDAY_DIFF( {Application Started Processing}, DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm') ) * 24) * 3600)) - 86400
    )  + 
    (HOUR(DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm')) * 3600) + (MINUTE(DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Brisbane'), 'M/D/YYYY h:mm')) * 60) + 
    (0 - ((HOUR(DATETIME_FORMAT(SET_TIMEZONE({Application Started Processing}, 'Australia/Brisbane'), 'M/D/YYYY h:mm')) + (MINUTE(DATETIME_FORMAT(SET_TIMEZONE({Application Started Processing}, 'Australia/Brisbane'), 'M/D/YYYY h:mm')) / 60)) * 3600))
),
0

)

Believe it or not, I’ve got about half a dozen variations of this all working, and the boss is happy, except for one issue: if someone starts working at 8:30 am, the current workday hasn’t “kicked in” yet, so it throws the formula out. Any applications that start processing after 10 am work fine (I think the cutoff is 9am in the WORKDAY_DIFF() function, but daylight savings is causing havoc…I think? The rest of the east coast of AU is one hour ahead).

Screenshot of the issue:

Screen Shot 2021-01-29 at 11.24.49 am

As you can see, any application that started after 10:00 am is showing the correct value.

QUESTION:
Firstly, is there an easier way to handle the above formula? It’s obviously very messy, and as a programmer that likes to keep his code DRY, it hurts my soul. Secondly, is there an easier way to do what I want to achieve?

1 Solution

Accepted Solutions
Matthew_Neal
5 - Automation Enthusiast
5 - Automation Enthusiast

Solved it. Feel free to reply to this if you want info on how. It wasn’t easy!

See Solution in Thread

1 Reply 1
Matthew_Neal
5 - Automation Enthusiast
5 - Automation Enthusiast

Solved it. Feel free to reply to this if you want info on how. It wasn’t easy!