Forum Discussion

StuartKemp's avatar
StuartKemp
Qrew Member
6 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
Relationships
PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
6 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
6 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
------------------------------