Removing Holidays and Weekends between two dates and calculating days

  • 0
  • 2
  • Question
  • Updated 8 months ago
  • In Progress
  • (Edited)
Each year i will have number of hoildays , so i need to add in a table as records so from there i need to fetch the holiday dates and calculate in another table where the two date fields are there. Is there any native quickbase option.
Photo of John Thomas

John Thomas

  • 1,082 Points 1k badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of David

David

  • 294 Points 250 badge 2x thumb
I just successfully did this for a vacation request App that I made.  I used a much simpler method.  In the home settings of your App you can create App wide variables that you can use like normal fields.  This gives you one place to update the holidays (since they change every year) and you don't have to edit formula fields or search among other fields to find the holidays.
 
Then I created a formula field called "Holiday Adjustment" to calculate any holidays that might fall between the start date and end date of my vacation request:

Sum(
If([Start Date]<=ToDate([New Year's Day]),If([End Date]>=ToDate([New Year's Day]),1,0),0),
If([Start Date]<=ToDate([Memorial Day]),If([End Date]>=ToDate([Memorial Day]),1,0),0),
If([Start Date]<=ToDate([4th of July]),If([End Date]>=ToDate([4th of July]),1,0),0),
If([Start Date]<=ToDate([Labor Day]),If([End Date]>=ToDate([Labor Day]),1,0),0),
If([Start Date]<=ToDate([Thanksgiving]),If([End Date]>=ToDate([Thanksgiving]),1,0),0),
If([Start Date]<=ToDate([Black Friday]),If([End Date]>=ToDate([Black Friday]),1,0),0),
If([Start Date]<=ToDate([Christmas Eve]),If([End Date]>=ToDate([Christmas Eve]),1,0),0),
If([Start Date]<=ToDate([Christmas Day]),If([End Date]>=ToDate([Christmas Day]),1,0),0),
If([Start Date]<=ToDate([New Year's Eve]),If([End Date]>=ToDate([New Year's Eve]),1,0),0)
)

This will sum up all the Holidays that fall between the start date and end date.  Then, when I calculated the # of vacation days needed to fill in the time between the Start Date and End Date, I was able to subtract out the Holiday Adjustment number:

WeekdaySub([End Date],[Start Date])+1-[Holiday Adjustment]

I have a +1 there because if the employee only takes 1 day off, the Start Date and End Date will be the same, and the WeekdaySub formula will return a 0.

Hope this helps.
(Edited)