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

[Matthew_Neal]

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:

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:

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?

[Matthew_Neal · Accepted solution]

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