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.
 1,082 Points
Posted 2 years ago
 294 Points
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.
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)
Related Categories

Formulas & functions
 2906 Conversations
 73 Followers