Creating a Service Level Check

[GaymerTrav]

Hello,

Thanks in advance for any assistance with this.

We have a ticket system whereby people submit by form an action and it's completed with a time stamp; with a goal of completing every request within 8 hours.

Currently we determine if the time completed was within 8 hours of it being submitted and that generates a "1" in a formula column.  That column provides a percentage of the amount of 1's filled and gives us a service level.

Recently I was asked to refine this solution to only consider submissions during business hours (Mon-Fri between 9am-6pm).

This way, anything submitted outside of business hours doesn't count against the 8 hour service level until it's within spec.

If something is submitted at 6:01pm, the timer freezes until 9am the next day given it's not a weekend.

Alternately as long as something is submitted within business hours the timer does not stop even if it exceeds business hours or goes into a weekend.

I'm hoping to get some ideas on how this can work within airtable or at all. I've tried a few things but not incredibly savvy with formulas and programming. Currently using the pro version of airtable.

Thanks

[Dimitris_Goudis]

Hey @GaymerTrav,

I to build that, in my opinion you have to choose one of the following options: 

1. Formula field
Using created at field you can track the time the form was submitted and with the last modified time you can track the time the request was completed. Then using a formula you can calculate the date time difference between those two date time fields. In your formula, you can set up conditional rules with with if or even with switch formula so then the formula will return the result

2. Scripting -> more complicated
Again you need the last modified time field and created at  fields and an automation to run when the request turns to completed. The script will run and do the calculations I described you in the option of formulas. This is the same solution in a different way and I recommend it only if you are more familiar with JS than Airtable formulas

For any kind of help please do not hesitate to text back.

Yours sincerely,
Dimitris Goudis 

[GaymerTrav]

Hey Dimitris

Thanks for your reply. I've tried various attempts using the formula field to no avail. The challenge is in getting the timers to behave accordingly

[Dimitris_Goudis]

Hey @GaymerTrav, 

- Did you add a create at field in the table with requests? 
- Did you add the last modified time for the status of the request? 

If those fields exist then most probably you are not doing correctly the formula with if. So I would recommend to type on paper all conditions and the start building from scratch step by step. 

Please feel free to contact me and do that together 🙂 

Yours sincerely, 
Dimitris Goudis 

[GaymerTrav]

Hey Dimitris,

Thanks for the offer to this together 😃
I actually got it to work (somewhat) just reverting to a more simple formula:

IF(

    AND(

        {Time Entered},

        {Completed Time}

    ),

    DATETIME_DIFF(

        {Completed Time},

        IF(

            HOUR({Time Entered}) < 9,

            DATEADD({Time Entered}, 9 - HOUR({Time Entered}), 'hours'),

            IF(

                HOUR({Time Entered}) >= 18,

                DATEADD(DATEADD({Time Entered}, 1, 'days'), 9 - HOUR({Time Entered}), 'hours'),

                {Time Entered}

            )

        ),

        'minutes'

    )

)

---

The only issue is that it doesn't account for the weekends. I could just do more math but I feel there has to be a simpler way.