Forum Discussion

MichaelQuist's avatar
MichaelQuist
Qrew Trainee
8 years ago

Any Ideas on how I can calculate an SLA Date based on business hours?

I have a problem I can't seem to solve with QuickBase.  I have to calculate an SLA date (date due) for a job in QuickBase based on the date it was created.  SLA Hours and Business Hours can vary from one job to another.  I have those values in the table so they can be used easily, however I need to be able to use those values to calculate when the SLA date is.  For instance..

Job 1 was created Monday, August 7th at 13:00.  Job 1 has an SLA of 30 business hours.  For this job, the business hours are:
Monday - Friday 8am to 6pm
Saturday and Sunday 8am to 4pm.
If the job was created outside business hours it falls to the closest 8am (in this case).  I have another field created that is calculating that already, called [Calculated SLA Start]

Now.. manually i can take that job and say.. ok here are the hours available to use for this..
Monday has 5 hours left to use.. = 5
Tuesday has 10 hours left to use.. +10 = 15
Wednesday has 10 hours left to use.. +10 = 25
Thursday has 10 hours available.. but since 30-25 = 5 i only need 5 hours of it..

Using the above.. the SLA date would be Thursday at 13:00  (8am business hours Thurs + 5 hours = 13:00 or 1PM.)

I would love to be able to use a formula to do this.  If you decide to submit ideas.. please remember that SLA's can be anywhere between 0 and 720 and Business Hours vary from job to job.. some don't include Saturday, some don't include both Weekend days.. some are only Wednesday to Sunday with varying times.  So it's fairly complex.

Any ideas on how I can achieve this?  Custom Functions?  While->Loop?  My own custom Javascript?
App builders
Ask about Quickbase functionality
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    ..the formula can be done natively, but it will be a very long formula.  I think that you need to hope that someone has done a similar formula  and offers to either post it or to work with you on a consulting basis to get it working with you.
  • MichaelQuist's avatar
    MichaelQuist
    Qrew Trainee
    8 years ago
    I wrote my own formulas to do this.. and they seem to be accurate the way I did this.  The formula may be written in a Novice manner so I apologize, I'm not a QuickBase expert so if this can be re-written to be better please by all means do so.

    I'm only posting this so that others can use it if they find it useful.

    First: I created a field in my table called [SLA Start Date]  This date is what will be used to base the SLA Hours off of.  I use this first formula to populate the SLA Start Date field.  It basically checks the business hours (SLA Hours) for each day and set's it to the right date/time accordingly.  

    Example: SLA Hours is 9:00am to 8:00pm.. a call created at 8:00am will show the SLA Start as 9:00am the same day it's created.. a call created at 8:30pm will show the SLA Start as 9:00am the next valid SLA day.
  • MichaelQuist's avatar
    MichaelQuist
    Qrew Trainee
    8 years ago
    var Number Sun = 0;
    var Number Mon = 1;
    var Number Tues= 2;
    var Number Wed = 3;
    var Number Thurs = 4;
    var Number Fri = 5;
    var Number Sat = 6;

    var Number SLAStartingDay = DayOfWeek(ToDate([Date Created]));

    var Text MonBHStart = ToText([Call Type - SLA Hours Start - Monday]);
    var Text MonBHEnd = ToText([Call Type - SLA Hours End - Monday]);
    var Text TuesBHStart = ToText([Call Type - SLA Hours Start - Tuesday]);
    var Text TuesBHEnd = ToText([Call Type - SLA Hours End - Tuesday]);
    var Text WedBHStart = ToText([Call Type - SLA Hours Start - Wednesday]);
    var Text WedBHEnd = ToText([Call Type - SLA Hours End - Wednesday]);
    var Text ThursBHStart = ToText([Call Type - SLA Hours Start - Thursday]);
    var Text ThursBHEnd = ToText([Call Type - SLA Hours End - Thursday]);
    var Text FriBHStart = ToText([Call Type - SLA Hours Start - Friday]);
    var Text FriBHEnd = ToText([Call Type - SLA Hours End - Friday]);
    var Text SatBHStart = ToText([Call Type - SLA Hours Start - Saturday]);
    var Text SatBHEnd = ToText([Call Type - SLA Hours End - Saturday]);
    var Text SunBHStart = ToText([Call Type - SLA Hours Start - Sunday]);
    var Text SunBHEnd = ToText([Call Type - SLA Hours End - Sunday]);

    var Text DayOneBHStart = if($SLAStartingDay = $Mon, $TuesBHStart, $SLAStartingDay = $Tues, $WedBHStart, $SLAStartingDay = $Wed, $ThursBHStart, $SLAStartingDay = $Thurs, $FriBHStart, $SLAStartingDay = $Fri, $SatBHStart, $SLAStartingDay = $Sat, $SunBHStart, $SLAStartingDay = $Sun, $MonBHStart, "08:00");
    var Text DayOneBHEnd = if($SLAStartingDay = $Mon, $TuesBHEnd, $SLAStartingDay = $Tues, $WedBHEnd, $SLAStartingDay = $Wed, $ThursBHEnd, $SLAStartingDay = $Thurs, $FriBHEnd, $SLAStartingDay = $Fri, $SatBHEnd, $SLAStartingDay = $Sat, $SunBHEnd, $SLAStartingDay = $Sun, $MonBHEnd, "18:00");
    var Text DayTwoBHStart = if($SLAStartingDay = $Mon, $WedBHStart, $SLAStartingDay = $Tues, $ThursBHStart, $SLAStartingDay = $Wed, $FriBHStart, $SLAStartingDay = $Thurs, $SatBHStart, $SLAStartingDay = $Fri, $SunBHStart, $SLAStartingDay = $Sat, $MonBHStart, $SLAStartingDay = $Sun, $TuesBHStart, "08:00");
    var Text DayTwoBHEnd = if($SLAStartingDay = $Mon, $WedBHEnd, $SLAStartingDay = $Tues, $ThursBHEnd, $SLAStartingDay = $Wed, $FriBHEnd, $SLAStartingDay = $Thurs, $SatBHEnd, $SLAStartingDay = $Fri, $SunBHEnd, $SLAStartingDay = $Sat, $MonBHEnd, $SLAStartingDay = $Sun, $TuesBHEnd, "18:00");
    var Text DayThreeBHStart = if($SLAStartingDay = $Mon, $ThursBHStart, $SLAStartingDay = $Tues, $FriBHStart, $SLAStartingDay = $Wed, $SatBHStart, $SLAStartingDay = $Thurs, $SunBHStart, $SLAStartingDay = $Fri, $MonBHStart, $SLAStartingDay = $Sat, $TuesBHStart, $SLAStartingDay = $Sun, $WedBHStart, "08:00");
    var Text DayThreeBHEnd = if($SLAStartingDay = $Mon, $ThursBHEnd, $SLAStartingDay = $Tues, $FriBHEnd, $SLAStartingDay = $Wed, $SatBHEnd, $SLAStartingDay = $Thurs, $SunBHEnd, $SLAStartingDay = $Fri, $MonBHEnd, $SLAStartingDay = $Sat, $TuesBHEnd, $SLAStartingDay = $Sun, $WedBHEnd, "18:00");
    var Text DayFourBHStart = if($SLAStartingDay = $Mon, $FriBHStart, $SLAStartingDay = $Tues, $SatBHStart, $SLAStartingDay = $Wed, $SunBHStart, $SLAStartingDay = $Thurs, $MonBHStart, $SLAStartingDay = $Fri, $TuesBHStart, $SLAStartingDay = $Sat, $WedBHStart, $SLAStartingDay = $Sun, $ThursBHStart, "08:00");
    var Text DayFourBHEnd = if($SLAStartingDay = $Mon, $FriBHEnd, $SLAStartingDay = $Tues, $SatBHEnd, $SLAStartingDay = $Wed, $SunBHEnd, $SLAStartingDay = $Thurs, $MonBHEnd, $SLAStartingDay = $Fri, $TuesBHEnd, $SLAStartingDay = $Sat, $WedBHEnd, $SLAStartingDay = $Sun, $ThursBHEnd, "18:00");
    var Text DayFiveBHStart = if($SLAStartingDay = $Mon, $SatBHStart, $SLAStartingDay = $Tues, $SunBHStart, $SLAStartingDay = $Wed, $MonBHStart, $SLAStartingDay = $Thurs, $TuesBHStart, $SLAStartingDay = $Fri, $WedBHStart, $SLAStartingDay = $Sat, $ThursBHStart, $SLAStartingDay = $Sun, $FriBHStart, "08:00");
    var Text DayFiveBHEnd = if($SLAStartingDay = $Mon, $SatBHEnd, $SLAStartingDay = $Tues, $SunBHEnd, $SLAStartingDay = $Wed, $MonBHEnd, $SLAStartingDay = $Thurs, $TuesBHEnd, $SLAStartingDay = $Fri, $WedBHEnd, $SLAStartingDay = $Sat, $ThursBHEnd, $SLAStartingDay = $Sun, $FriBHEnd, "18:00");
    var Text DaySixBHStart = if($SLAStartingDay = $Mon, $SunBHStart, $SLAStartingDay = $Tues, $MonBHStart, $SLAStartingDay = $Wed, $TuesBHStart, $SLAStartingDay = $Thurs, $WedBHStart, $SLAStartingDay = $Fri, $ThursBHStart, $SLAStartingDay = $Sat, $FriBHStart, $SLAStartingDay = $Sun, $SatBHStart, "08:00");
    var Text DaySixBHEnd = if($SLAStartingDay = $Mon, $SunBHEnd, $SLAStartingDay = $Tues, $MonBHEnd, $SLAStartingDay = $Wed, $TuesBHEnd, $SLAStartingDay = $Thurs, $WedBHEnd, $SLAStartingDay = $Fri, $ThursBHEnd, $SLAStartingDay = $Sat, $FriBHEnd, $SLAStartingDay = $Sun, $SatBHEnd, "18:00");
    var Text DaySevenBHStart = if($SLAStartingDay = $Mon, $MonBHStart, $SLAStartingDay = $Tues, $TuesBHStart, $SLAStartingDay = $Wed, $WedBHStart, $SLAStartingDay = $Thurs, $ThursBHStart, $SLAStartingDay = $Fri, $FriBHStart, $SLAStartingDay = $Sat, $SatBHStart, $SLAStartingDay = $Sun, $SunBHStart, "08:00");
    var Text DaySevenBHEnd = if($SLAStartingDay = $Mon, $MonBHEnd, $SLAStartingDay = $Tues, $TuesBHEnd, $SLAStartingDay = $Wed, $WedBHEnd, $SLAStartingDay = $Thurs, $ThursBHEnd, $SLAStartingDay = $Fri, $FriBHEnd, $SLAStartingDay = $Sat, $SatBHEnd, $SLAStartingDay = $Sun, $SunBHEnd, "18:00");


    If(

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DaySevenBHStart) <> ToTimeOfDay($DaySevenBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DaySevenBHStart)),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DayOneBHStart) <> ToTimeOfDay($DayOneBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayOneBHStart)) + Days(1),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DayTwoBHStart) <> ToTimeOfDay($DayTwoBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayTwoBHStart)) + Days(2),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DayThreeBHStart) <> ToTimeOfDay($DayThreeBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayThreeBHStart)) + Days(3),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DayFourBHStart) <> ToTimeOfDay($DayFourBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayFourBHStart)) + Days(4),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DayFiveBHStart) <> ToTimeOfDay($DayFiveBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayFiveBHStart)) + Days(5),

    ToTimeOfDay($DaySevenBHStart) > ToTimeOfDay([Date Created]) and ToTimeOfDay($DaySixBHStart) <> ToTimeOfDay($DaySixBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DaySixBHStart)) + Days(6),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DayOneBHStart) <> ToTimeOfDay($DayOneBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayOneBHStart)) + Days(1),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DayTwoBHStart) <> ToTimeOfDay($DayTwoBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayTwoBHStart)) + Days(2),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DayThreeBHStart) <> ToTimeOfDay($DayThreeBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayThreeBHStart)) + Days(3),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DayFourBHStart) <> ToTimeOfDay($DayFourBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayFourBHStart)) + Days(4),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DayFiveBHStart) <> ToTimeOfDay($DayFiveBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DayFiveBHStart)) + Days(5),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DaySixBHStart) <> ToTimeOfDay($DaySixBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DaySixBHStart)) + Days(6),

    ToTimeOfDay($DaySevenBHEnd) < ToTimeOfDay([Date Created]) and ToTimeOfDay($DaySevenBHStart) <> ToTimeOfDay($DaySevenBHEnd),
    ToTimestamp(ToDate([Date Created]), ToTimeOfDay($DaySevenBHStart)) + Days(7),

    // General Case
    [Date Created]
    )
  • MichaelQuist's avatar
    MichaelQuist
    Qrew Trainee
    8 years ago
    Then.. I created a field in the table called [SLA End Date - Tier 1] and other fields that I use to populate the SLA Hours for each type of call.. [Call Type - SLA Hours Start - Monday] and [Call Type - SLA Hours End - Monday] for each day of the week.. and then also [Call Type - SLA Hours - Tier 1] which contains the number of hours to use.. then also a [Call Type - SLA Calculation Method] which is either SLA or SLA - EOD, those are my two types of SLA methods.. one just uses straight up SLA Hours and one uses the end of day no matter what after applying the SLA Hours.

    The formula is crazy but I didn't know how to loop so there is just a bunch of nested If's..
  • MichaelQuist's avatar
    MichaelQuist
    Qrew Trainee
    8 years ago
    var Number Sun = 0;
    var Number Mon = 1;
    var Number Tues= 2;
    var Number Wed = 3;
    var Number Thurs = 4;
    var Number Fri = 5;
    var Number Sat = 6;

    var Duration SLADuration = [Call Type - SLA Hours - Tier 1];
    var Number SLAStartingDay = DayOfWeek(ToDate([SLA Start Date]));
    var Text SLACalculationMethod = [Call Type - SLA Calculation Method];

    var Text MonBHStart = ToText([Call Type - SLA Hours Start - Monday]);
    var Text MonBHEnd = ToText([Call Type - SLA Hours End - Monday]);
    var Text TuesBHStart = ToText([Call Type - SLA Hours Start - Tuesday]);
    var Text TuesBHEnd = ToText([Call Type - SLA Hours End - Tuesday]);
    var Text WedBHStart = ToText([Call Type - SLA Hours Start - Wednesday]);
    var Text WedBHEnd = ToText([Call Type - SLA Hours End - Wednesday]);
    var Text ThursBHStart = ToText([Call Type - SLA Hours Start - Thursday]);
    var Text ThursBHEnd = ToText([Call Type - SLA Hours End - Thursday]);
    var Text FriBHStart = ToText([Call Type - SLA Hours Start - Friday]);
    var Text FriBHEnd = ToText([Call Type - SLA Hours End - Friday]);
    var Text SatBHStart = ToText([Call Type - SLA Hours Start - Saturday]);
    var Text SatBHEnd = ToText([Call Type - SLA Hours End - Saturday]);
    var Text SunBHStart = ToText([Call Type - SLA Hours Start - Sunday]);
    var Text SunBHEnd = ToText([Call Type - SLA Hours End - Sunday]);

    var Text DayOneBHStart = if($SLAStartingDay = $Mon, $TuesBHStart, $SLAStartingDay = $Tues, $WedBHStart, $SLAStartingDay = $Wed, $ThursBHStart, $SLAStartingDay = $Thurs, $FriBHStart, $SLAStartingDay = $Fri, $SatBHStart, $SLAStartingDay = $Sat, $SunBHStart, $SLAStartingDay = $Sun, $MonBHStart, "08:00");
    var Text DayOneBHEnd = if($SLAStartingDay = $Mon, $TuesBHEnd, $SLAStartingDay = $Tues, $WedBHEnd, $SLAStartingDay = $Wed, $ThursBHEnd, $SLAStartingDay = $Thurs, $FriBHEnd, $SLAStartingDay = $Fri, $SatBHEnd, $SLAStartingDay = $Sat, $SunBHEnd, $SLAStartingDay = $Sun, $MonBHEnd, "18:00");
    var Text DayTwoBHStart = if($SLAStartingDay = $Mon, $WedBHStart, $SLAStartingDay = $Tues, $ThursBHStart, $SLAStartingDay = $Wed, $FriBHStart, $SLAStartingDay = $Thurs, $SatBHStart, $SLAStartingDay = $Fri, $SunBHStart, $SLAStartingDay = $Sat, $MonBHStart, $SLAStartingDay = $Sun, $TuesBHStart, "08:00");
    var Text DayTwoBHEnd = if($SLAStartingDay = $Mon, $WedBHEnd, $SLAStartingDay = $Tues, $ThursBHEnd, $SLAStartingDay = $Wed, $FriBHEnd, $SLAStartingDay = $Thurs, $SatBHEnd, $SLAStartingDay = $Fri, $SunBHEnd, $SLAStartingDay = $Sat, $MonBHEnd, $SLAStartingDay = $Sun, $TuesBHEnd, "18:00");
    var Text DayThreeBHStart = if($SLAStartingDay = $Mon, $ThursBHStart, $SLAStartingDay = $Tues, $FriBHStart, $SLAStartingDay = $Wed, $SatBHStart, $SLAStartingDay = $Thurs, $SunBHStart, $SLAStartingDay = $Fri, $MonBHStart, $SLAStartingDay = $Sat, $TuesBHStart, $SLAStartingDay = $Sun, $WedBHStart, "08:00");
    var Text DayThreeBHEnd = if($SLAStartingDay = $Mon, $ThursBHEnd, $SLAStartingDay = $Tues, $FriBHEnd, $SLAStartingDay = $Wed, $SatBHEnd, $SLAStartingDay = $Thurs, $SunBHEnd, $SLAStartingDay = $Fri, $MonBHEnd, $SLAStartingDay = $Sat, $TuesBHEnd, $SLAStartingDay = $Sun, $WedBHEnd, "18:00");
    var Text DayFourBHStart = if($SLAStartingDay = $Mon, $FriBHStart, $SLAStartingDay = $Tues, $SatBHStart, $SLAStartingDay = $Wed, $SunBHStart, $SLAStartingDay = $Thurs, $MonBHStart, $SLAStartingDay = $Fri, $TuesBHStart, $SLAStartingDay = $Sat, $WedBHStart, $SLAStartingDay = $Sun, $ThursBHStart, "08:00");
    var Text DayFourBHEnd = if($SLAStartingDay = $Mon, $FriBHEnd, $SLAStartingDay = $Tues, $SatBHEnd, $SLAStartingDay = $Wed, $SunBHEnd, $SLAStartingDay = $Thurs, $MonBHEnd, $SLAStartingDay = $Fri, $TuesBHEnd, $SLAStartingDay = $Sat, $WedBHEnd, $SLAStartingDay = $Sun, $ThursBHEnd, "18:00");
    var Text DayFiveBHStart = if($SLAStartingDay = $Mon, $SatBHStart, $SLAStartingDay = $Tues, $SunBHStart, $SLAStartingDay = $Wed, $MonBHStart, $SLAStartingDay = $Thurs, $TuesBHStart, $SLAStartingDay = $Fri, $WedBHStart, $SLAStartingDay = $Sat, $ThursBHStart, $SLAStartingDay = $Sun, $FriBHStart, "08:00");
    var Text DayFiveBHEnd = if($SLAStartingDay = $Mon, $SatBHEnd, $SLAStartingDay = $Tues, $SunBHEnd, $SLAStartingDay = $Wed, $MonBHEnd, $SLAStartingDay = $Thurs, $TuesBHEnd, $SLAStartingDay = $Fri, $WedBHEnd, $SLAStartingDay = $Sat, $ThursBHEnd, $SLAStartingDay = $Sun, $FriBHEnd, "18:00");
    var Text DaySixBHStart = if($SLAStartingDay = $Mon, $SunBHStart, $SLAStartingDay = $Tues, $MonBHStart, $SLAStartingDay = $Wed, $TuesBHStart, $SLAStartingDay = $Thurs, $WedBHStart, $SLAStartingDay = $Fri, $ThursBHStart, $SLAStartingDay = $Sat, $FriBHStart, $SLAStartingDay = $Sun, $SatBHStart, "08:00");
    var Text DaySixBHEnd = if($SLAStartingDay = $Mon, $SunBHEnd, $SLAStartingDay = $Tues, $MonBHEnd, $SLAStartingDay = $Wed, $TuesBHEnd, $SLAStartingDay = $Thurs, $WedBHEnd, $SLAStartingDay = $Fri, $ThursBHEnd, $SLAStartingDay = $Sat, $FriBHEnd, $SLAStartingDay = $Sun, $SatBHEnd, "18:00");
    var Text DaySevenBHStart = if($SLAStartingDay = $Mon, $MonBHStart, $SLAStartingDay = $Tues, $TuesBHStart, $SLAStartingDay = $Wed, $WedBHStart, $SLAStartingDay = $Thurs, $ThursBHStart, $SLAStartingDay = $Fri, $FriBHStart, $SLAStartingDay = $Sat, $SatBHStart, $SLAStartingDay = $Sun, $SunBHStart, "08:00");
    var Text DaySevenBHEnd = if($SLAStartingDay = $Mon, $MonBHEnd, $SLAStartingDay = $Tues, $TuesBHEnd, $SLAStartingDay = $Wed, $WedBHEnd, $SLAStartingDay = $Thurs, $ThursBHEnd, $SLAStartingDay = $Fri, $FriBHEnd, $SLAStartingDay = $Sat, $SatBHEnd, $SLAStartingDay = $Sun, $SunBHEnd, "18:00");

    var DateTime SLAStartingDate = If($SLACalculationMethod = "SLA - EOD", ToTimestamp(ToDate([SLA Start Date]), ToTimeOfDay($DaySevenBHEnd)), [SLA Start Date]);

    var Duration DayZeroBHAvailable = If(ToTimeOfDay($DaySevenBHEnd) >= ToTimeOfDay($SLAStartingDate) and ToTimeOfDay($DaySevenBHEnd) <> ToTimeOfDay("23:59"), (ToTimeOfDay($DaySevenBHEnd) - ToTimeOfDay($SLAStartingDate)), ToTimeOfDay($DaySevenBHEnd) >= ToTimeOfDay($SLAStartingDate), (ToTimeOfDay($DaySevenBHEnd) - ToTimeOfDay($SLAStartingDate)) + Minutes(1), Hours(0));
    var Duration DayOneBHAvailable = If(ToTimeOfDay($DayOneBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DayOneBHEnd) - ToTimeOfDay($DayOneBHStart)) + Minutes(1), ToTimeOfDay($DayOneBHEnd) - ToTimeOfDay($DayOneBHStart));
    var Duration DayTwoBHAvailable = If(ToTimeOfDay($DayTwoBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DayTwoBHEnd) - ToTimeOfDay($DayTwoBHStart)) + Minutes(1), ToTimeOfDay($DayTwoBHEnd) - ToTimeOfDay($DayTwoBHStart));
    var Duration DayThreeBHAvailable = If(ToTimeOfDay($DayThreeBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DayThreeBHEnd) - ToTimeOfDay($DayThreeBHStart)) + Minutes(1), ToTimeOfDay($DayThreeBHEnd) - ToTimeOfDay($DayThreeBHStart));
    var Duration DayFourBHAvailable = If(ToTimeOfDay($DayFourBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DayFourBHEnd) - ToTimeOfDay($DayFourBHStart)) + Minutes(1), ToTimeOfDay($DayFourBHEnd) - ToTimeOfDay($DayFourBHStart));
    var Duration DayFiveBHAvailable = If(ToTimeOfDay($DayFiveBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DayFiveBHEnd) - ToTimeOfDay($DayFiveBHStart)) + Minutes(1), ToTimeOfDay($DayFiveBHEnd) - ToTimeOfDay($DayFiveBHStart));
    var Duration DaySixBHAvailable = If(ToTimeOfDay($DaySixBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DaySixBHEnd) - ToTimeOfDay($DaySixBHStart)) + Minutes(1), ToTimeOfDay($DaySixBHEnd) - ToTimeOfDay($DaySixBHStart));
    var Duration DaySevenBHAvailable = If(ToTimeOfDay($DaySevenBHEnd) = ToTimeOfDay("23:59"), (ToTimeOfDay($DaySevenBHEnd) - ToTimeOfDay($DaySevenBHStart)) + Minutes(1), ToTimeOfDay($DaySevenBHEnd) - ToTimeOfDay($DaySevenBHStart));

    If(

    ToMinutes($SLADuration) <= ToMinutes($DayZeroBHAvailable) and ToMinutes($DayZeroBHAvailable) >= 0,
    $SLAStartingDate + Minutes(ToMinutes($SLADuration)),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)),
    $SLAStartingDate + Days(1) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DayOneBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable)),
    $SLAStartingDate + Days(2) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DayTwoBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable)),
    $SLAStartingDate + Days(3) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DayThreeBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable)),
    $SLAStartingDate + Days(4) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DayFourBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable)),
    $SLAStartingDate + Days(5) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DayFiveBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable)),
    $SLAStartingDate + Days(6) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySixBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(7) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(8) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable) + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(9) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable) + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(10) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable) + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(11) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable) + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(12) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable) + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)),
    $SLAStartingDate + Days(13) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable) + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(14) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable) + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(15) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*2 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(16) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*2 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(17) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*2 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(18) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*2 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(19) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*2 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*2),
    $SLAStartingDate + Days(20) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*2 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(21) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*2 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(22) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*3 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(23) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*3 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(24) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*3 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(25) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*3 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(26) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*3 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*3),
    $SLAStartingDate + Days(27) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*3 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(28) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*3 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(29) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*4 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(30) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*4 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(31) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*4 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(32) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*4 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(33) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*4 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*5 + ToMinutes($DaySevenBHAvailable)*4),
    $SLAStartingDate + Days(34) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*4 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*5 + ToMinutes($DaySevenBHAvailable)*5),
    $SLAStartingDate + Days(35) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*5 + ToMinutes($DaySevenBHAvailable)*4 + (ToMinutes(ToTimeOfDay($SLAStartingDate) - ToTimeOfDay($DaySevenBHStart)))))),

    ToMinutes($SLADuration) <= (ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*6 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*5 + ToMinutes($DaySevenBHAvailable)*5),
    $SLAStartingDate + Days(36) + Minutes(ToMinutes($SLADuration)-((ToMinutes($DayZeroBHAvailable) + ToMinutes($DayOneBHAvailable)*5 + ToMinutes($DayTwoBHAvailable)*5 + ToMinutes($DayThreeBHAvailable)*5 + ToMinutes($DayFourBHAvailable)*5 + ToMinutes($DayFiveBHAvailable)*5 + ToMinutes($DaySixBHAvailable)*5 + ToMinutes($DaySevenBHAvailable)*5 + (ToMi
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Wow!  Thx for posting that solution.  That must have taken a while to write and debug.
  • MichaelQuist's avatar
    MichaelQuist
    Qrew Trainee
    8 years ago
    I've got about 20-30 hours into it I suppose.. and i'm honestly surprised since SLA's are used in a ton of businesses that nobody has a solution out there, or that it's not built into a program like QuickBase already.  Anyway I hope that this can help someone else besides me :-)
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    I am speechless after seeing that formula and hearing you invested that amount of time in developing it. 



    You are probably thinking I am going to tell you it would take just a few lines of JavaScript to implement it but I think that is obvious. Instead I am going to show you a technique that might be used to shorten your native formula.

    One of the challenges that you probably faced is that it is natural to specify the available SLA schedule based on day of the week but you have to calculate the SLA based on the date/time a record is created. So there has to be some logic in your formulas that shifts the SLA schedule to start from the date/time a record is created. This is where you can emulate having an array in the formula language using a delimited string.

    To exemplify this consider this partial formula which represents the SLA schedule using seven variables:
    var Text Sun = "08-16";
    var Text Mon = "09-16";
    var Text Tue = "10-16";
    var Text Wed = "11-16";
    var Text Thu = "12-16";
    var Text Fri = "13-16";
    var Text Sat = "14-16";
    var Number SLAHours = 30; 
    var Text Array = List(";", $Sun, $Mon, $Tue, $Wed, $Thu, $Fri, $Sat) & ";";
     var Number Length = Length($Array);
     var Number DayOfWeek = DayOfWeek(ToDate([Start Date Time]));
    var Number SLAStart = Hour(ToTimeOfDay([Start Date Time])) + 
                          Minute(ToTimeOfDay([Start Date Time])) / 60 +
                          Second(ToTimeOfDay([Start Date Time])) / 3600 +
                          MSecond(ToTimeOfDay([Start Date Time])) / 3600000;
     var Number Shift = 6 * $DayOfWeek;
    var Text RotatedArray = NotLeft($Array, $Shift) & Left($Array, $Shift); 
    var Number SA = ToNumber(Part(Part($RotatedArray, 1, ";"), 1, "-"));
    var Number EA = ToNumber(Part(Part($RotatedArray, 1, ";"), 2, "-"));
     var Number SB = ToNumber(Part(Part($RotatedArray, 2, ";"), 1, "-"));
    var Number EB = ToNumber(Part(Part($RotatedArray, 2, ";"), 2, "-"));
     var Number SC = ToNumber(Part(Part($RotatedArray, 3, ";"), 1, "-"));
    var Number EC = ToNumber(Part(Part($RotatedArray, 3, ";"), 2, "-"));
     var Number SD = ToNumber(Part(Part($RotatedArray, 4, ";"), 1, "-"));
    var Number ED = ToNumber(Part(Part($RotatedArray, 4, ";"), 2, "-"));
     var Number SE = ToNumber(Part(Part($RotatedArray, 5, ";"), 1, "-"));
    var Number EE = ToNumber(Part(Part($RotatedArray, 5, ";"), 2, "-"));
     var Number SF = ToNumber(Part(Part($RotatedArray, 6, ";"), 1, "-"));
    var Number EF = ToNumber(Part(Part($RotatedArray, 6, ";"), 2, "-"));
     var Number SG = ToNumber(Part(Part($RotatedArray, 7, ";"), 1, "-"));
    var Number EG = ToNumber(Part(Part($RotatedArray, 7, ";"), 2, "-"));
    The script emulates a doubly indexed array and indexes into it using Part() to parse out the start and ending times for each day by looking for the ":" and "-" separator characters. Your script can be vastly shortened if you use this "string array" trick.

    However, I think this is just another case where the "no code" / "low code" myth rears its ugly head. I would use script to implement this SLA. In fact, for over ten years I have encouraged QuickBase to all together replace their formula language with script.