Forum Discussion
DavidPontier1
7 years agoQrew Cadet
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.
- ChrisNewsome5 years agoQrew CaptainI'm trying to get Holidays to show up in a calendar report, do you know of a way to get these variables to affect that? For instance, we have a table called "activities" that each user looks at a calendar report from to know what they're doing day to day. I'd like to have a way to show holidays so they don't mistakenly schedule things on a day off.
------------------------------
Thanks,
Chris Newsome
------------------------------- MarkShnier__You5 years ago
Qrew Legend
Are you looking for a way to have an entry for each Employee for each holiday?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- ChrisNewsome5 years agoQrew CaptainHey Mark. Ideally, I'd rather just block the day off for everyone.
------------------------------
Thanks,
Chris Newsome
------------------------------