Forum Discussion

Qrew Member

5 years ago

Calculate total working days between dates (including Bank holidays)

Hi I am aiming to calculate the total amount of working days between two dates whilst also considering Weekends, Bank holidays and Christmas Holiday etc. I have created a calendar for the next 5 yea...

Quickbase Automations

Qrew Assistant Captain

5 years ago

The first part is pretty straightforward. The traditional work days can be calculated as follows:

ToDays(ToWeekdayP(endDate) - ToWeekdayN(startDate)) - Int(ToDays(ToWeekdayP(endDate) - ToWeekdayN(startDate)) / 7) * 2 + 1

Since each company observes different holidays, excluding the holidays would be a simple matter of setting up variables for the holidays and subtracting the total number of holidays that fall between the start and end dates.

------------------------------
Paul Peterson
------------------------------

MarkShnier__You
Qrew Legend
5 years ago
Re:

excluding the holidays would be a simple matter of ....

Actually, this is actually not an easy problem to solve using formuals. Al other solutions I have seen and implemented myself are not elegant and involve tons of code and formulas.

i

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- StuartKemp
  Qrew Member
  5 years ago
  Hi Mark & Paul,
  Thank you.
  With some help we have tackled the first challenge by using two automations to and from for the relevant date range and it counts correctly.
  Now on to the challenge of returning the resulting date after adding X amount of working days depending on Weekends, Bank & Company Holiday fall within the amount of days.
  
  ------------------------------
  Stuart Kemp
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    5 years ago
    You first post suggested that you wanted to calculate the # of working days between two dates. Are you now asking a different question - to calculate the end date given the start date and the # of working days required?
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------