Forum Discussion

StuartKemp's avatar
StuartKemp
Qrew Member
4 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 years which puts a 1 next to the days that are actual working days.

Within this table the desired result is achieved by simply filtering the start and end date and it totals up the working days, however I need this result to appear as a result of a start and end date on another table.
Any help appreciated.

After I have achieved this I will also need to calculate the end date based on a start date and adding a number of working days, which again needs to consider Weekends, Bank holidays and Christmas Holiday etc.
Thank you in anticiption.

------------------------------
Stu
------------------------------
Quickbase Automations
Relationships

9 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    I recall seeing a nice post by a Quick Base staffer about this which was the most elegant solution so far, but I can't locate it.

    The gist of it is here


    You already have your Master Holidays Table.  Create a child table to your "Projects" table called Project Holidays.

    Set up an Automation that when a record is added or modified 
    1. Delete the Project Holidays (ie in case the Projects dates are modified we need to start fresh) 
    2. Copy the Holidays from the master Holidays table (just the ones flagged as Holidays) to the Project Holidays table where the dates meet the Project dates.
    3. The count of those children is the # of holidays in the Project date Span.


     

    ​

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    4 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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 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's avatar
        StuartKemp
        Qrew Member
        4 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
        ------------------------------