Forum Discussion

Annie_ChunjiCui's avatar
Annie_ChunjiCui
Qrew Trainee
6 years ago

How do i block off weekends and public holidays from processing time?

Hi, 

I am using below formula to exclude weekends from processing time between [Date and time of request hit inbox] and [Date and time of action taken].  On top of this, I would like to exclude public holidays too! So for example, if I choose country as US, then block off USpublic holidays. If i choose country as Japan, then block off Japan public holidays.. Is it possible to build such amazing formula?

Thank you!

Annie

My current formula: 

var timeofday DayStartTime = ToTimeOfDay("7:00 am");
var timeofday DayEndTime = ToTimeOfDay("6:00 pm");
var datetime StartClock = [Date and time of request hit inbox];
var datetime EndClock = [Date and time of action taken];
var DateTime StartDateTime = Max($StartClock, ToTimestamp(ToDate($StartClock), $DayStartTime));
var DateTime EndDateTimeTesting = If(IsNull($EndClock), Now() ,$EndClock);
var DateTime EndDateTime = Min($EndDateTimeTesting, ToTimestamp(ToDate($EndDateTimeTesting), $DayEndTime));
var Number WeekDayDays = WeekdaySub(ToDate($EndDateTime), ToDate($StartDateTime)) + 1;
var number HoursBeforeStartEndAdjustment = $WeekDayDays * ToHours($DayEndTime - $DayStartTime);
var datetime StartDateTimeBounded = If(ToTimeOfDay($StartDateTime) < $DayStartTime, ToTimestamp(ToDate($StartDateTime), $DayStartTime), $StartDateTime);
var datetime EndDateTimeBounded = If(ToTimeOfDay($EndDateTime) > $DayEndTime, ToTimestamp(ToDate($EndDateTime), $DayEndTime), $EndDateTime);
Max(0,Round($HoursBeforeStartEndAdjustment
- Max(0,ToHours(ToTimeOfDay($StartDateTimeBounded) - $DayStartTime))
- Max(0,ToHours($DayEndTime - ToTimeOfDay($EndDateTimeBounded))),0.1))

------------------------------
Annie
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    I can suggest an approach but I don't have ready made code to post.
    You could have a table of countries.  Each Country record would have a Country name and say go crazy with 20 slots for each of that Country's holidays.

    Then you make a relationships where 1 Country has many Claims. (Let call you records "Claims").  Look up the 20 holidays down to the claim.  You will also need to snapshot them since as time goes by you will be updating the Holiday dates and not not want to to change historical Claims.

    Then, you will need to have a field to count the # of holidays which fall in between your Claims Start and End  dates.​

    It would be a repetitive formula like

    Count(
    [Claim Start Date]<[Holiday 1 snapshot] and [Claim End Date]>[Holiday 1 snapshot]),
     [Claim Start Date]<[Holiday 2 snapshot] and [Claim End Date]>[Holiday 2 snapshot]),
    etc
    [Claim Start Date]<[Holiday 20 snapshot] and [Claim End Date]>[Holiday 20 snapshot]))

    so then you will have the # of days to subtract from your processing time, and you would multiply the # of days times the hours per day (which seems to be 11 hours = 1 day) and subtract that from your formula above.

     ​​

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------