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
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,226 Points
Can you tell me how many days out you need to look for holidays.
Is this looking say just a week out from a date to see if there are any holidays in that span, or is it a longer range between the dates?
Is this looking say just a week out from a date to see if there are any holidays in that span, or is it a longer range between the dates?
 1,082 Points
Entire Year
Basically i will have a table called "Off" in that table i will have date field "Hoilday Date" , so i will create 14 or 15 records each year . So those records will be my Holidays for the year.
I have another table "Records" , in that table i have two fields "Start" and "End" date fields , so i will select a date in both two fields , for example 15/09/2017  20/09/2017 so basically 16 and 17 are weekends and consider 18092017 [Which i added as a record in "Off" table] is a holiday , then i get the number of days as 3 [15 0917, 190917 , 200917] .
Is there any native quickbase option.
Many Thanks in advance
Basically i will have a table called "Off" in that table i will have date field "Hoilday Date" , so i will create 14 or 15 records each year . So those records will be my Holidays for the year.
I have another table "Records" , in that table i have two fields "Start" and "End" date fields , so i will select a date in both two fields , for example 15/09/2017  20/09/2017 so basically 16 and 17 are weekends and consider 18092017 [Which i added as a record in "Off" table] is a holiday , then i get the number of days as 3 [15 0917, 190917 , 200917] .
Is there any native quickbase option.
Many Thanks in advance
(Edited)
 372 Points
I actually was looking for an answer to this a little while ago and came up with a solution. I've got an app that needs to calculate an expiration of 15 business days from the date the case was opened, and it needs to work around a couple of preset holidays.
Here is what my team ended up doing:
First, we made a table called Holidays
We made ours with a single record containing each of the set dates. This is so a low code user can update the holiday calendar without needing to edit any formulas.
Note the denotation of Holiday 1 & 2 dates for Christmas and Thanksgiving the formula checks these in order, so Holiday 1 will always be the first day off on or around that holiday.
For example, in 2018 Christmas falls on a Tuesday, so most companies will take off that Monday (Christmas Eve) and that Tuesday. So in 2018, Christmas Holiday 1 will be 122418 and Holiday 2 will be 122518 (this will make more sense in the formula).
We made the key field of this record a number that we could reproduce in each of the related tables. In this example we have 34 tables that need to calculate this same expiration date. Each Holiday may have many records in the other tables.
So now we've got a table with Holidays, and the ability to reference each of these dates in our main tables. Next is the expiration formula:
var date laborday = [Labor Day];
var date thanksgiving = [Thanksgiving];
var date alsothanksgiving = [Day After Thanksgiving];
var date christmas = [Christmas Holiday 1];
var date alsochristmas = [Christmas Holiday 2];
var date newyears =[New Years Day];
var date memorialday =[Memorial Day];
var date independenceday =[Independence Day];
var number a = 14;
var date start = [Date Received];
var number b = $a + If($laborday >= $start and $laborday <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($thanksgiving >= $start and $thanksgiving <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($alsothanksgiving >= $start and $alsothanksgiving <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($christmas >= $start and $christmas <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($alsochristmas >= $start and $alsochristmas <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($newyears >= $start and $newyears <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($memorialday >= $start and $memorialday <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($independenceday >= $start and $independenceday <= WeekdayAdd($start, $h), 1, 0);
WeekdayAdd($start, $h)
Our standard expiration time for a case is 15 business days including the day it was received so when using the WeekDayAdd formula, this results in WeekDayAdd([Date Received], 14).
What this formula does is take our original expiration formula, checks each of our specified holidays to see if they fall within that range, and increments the work days by one each time we get a match.
Hope this is makes sense. So far it's worked like a dream and our managers (who have little technical acumen) are able to update the holiday calendar with ease.
Here is what my team ended up doing:
First, we made a table called Holidays
We made ours with a single record containing each of the set dates. This is so a low code user can update the holiday calendar without needing to edit any formulas.
Note the denotation of Holiday 1 & 2 dates for Christmas and Thanksgiving the formula checks these in order, so Holiday 1 will always be the first day off on or around that holiday.
For example, in 2018 Christmas falls on a Tuesday, so most companies will take off that Monday (Christmas Eve) and that Tuesday. So in 2018, Christmas Holiday 1 will be 122418 and Holiday 2 will be 122518 (this will make more sense in the formula).
We made the key field of this record a number that we could reproduce in each of the related tables. In this example we have 34 tables that need to calculate this same expiration date. Each Holiday may have many records in the other tables.
So now we've got a table with Holidays, and the ability to reference each of these dates in our main tables. Next is the expiration formula:
var date laborday = [Labor Day];
var date thanksgiving = [Thanksgiving];
var date alsothanksgiving = [Day After Thanksgiving];
var date christmas = [Christmas Holiday 1];
var date alsochristmas = [Christmas Holiday 2];
var date newyears =[New Years Day];
var date memorialday =[Memorial Day];
var date independenceday =[Independence Day];
var number a = 14;
var date start = [Date Received];
var number b = $a + If($laborday >= $start and $laborday <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($thanksgiving >= $start and $thanksgiving <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($alsothanksgiving >= $start and $alsothanksgiving <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($christmas >= $start and $christmas <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($alsochristmas >= $start and $alsochristmas <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($newyears >= $start and $newyears <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($memorialday >= $start and $memorialday <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($independenceday >= $start and $independenceday <= WeekdayAdd($start, $h), 1, 0);
WeekdayAdd($start, $h)
Our standard expiration time for a case is 15 business days including the day it was received so when using the WeekDayAdd formula, this results in WeekDayAdd([Date Received], 14).
What this formula does is take our original expiration formula, checks each of our specified holidays to see if they fall within that range, and increments the work days by one each time we get a match.
Hope this is makes sense. So far it's worked like a dream and our managers (who have little technical acumen) are able to update the holiday calendar with ease.
(Edited)
 556 Points
Really been trying to make this work, but it keeps throwing an error on me.
It says : "Formula Error  Incorrect variable type.The type of the variable does not match the return type of the expression." Not sure what I am doing wrong. May just need a new set of eyes on this...
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = [Start];
var number b = $a + If($newyearse >= $start and $newyearse <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($newyearsd >= $start and $newyearsd <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($easter >= $start and $easter <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($memorial >= $start and $memorial <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($julyth >= $start and $julyth <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($laborday >= $start and $laborday <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($thanksgivingt >= $start and $thanksgivingt <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($thanksgivingf >= $start and $thanksgivingf <= WeekdayAdd($start, $h), 1, 0);
var number j = $i + If($christmase >= $start and $christmase <= WeekdayAdd($start, $i), 1, 0);
var number k = $j + If($christmasd >= $start and $christmasd <= WeekdayAdd($start, $j), 1, 0);
WeekdayAdd($start, $j)
It says : "Formula Error  Incorrect variable type.The type of the variable does not match the return type of the expression." Not sure what I am doing wrong. May just need a new set of eyes on this...
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = [Start];
var number b = $a + If($newyearse >= $start and $newyearse <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($newyearsd >= $start and $newyearsd <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($easter >= $start and $easter <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($memorial >= $start and $memorial <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($julyth >= $start and $julyth <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($laborday >= $start and $laborday <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($thanksgivingt >= $start and $thanksgivingt <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($thanksgivingf >= $start and $thanksgivingf <= WeekdayAdd($start, $h), 1, 0);
var number j = $i + If($christmase >= $start and $christmase <= WeekdayAdd($start, $i), 1, 0);
var number k = $j + If($christmasd >= $start and $christmasd <= WeekdayAdd($start, $j), 1, 0);
WeekdayAdd($start, $j)
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,226 Points
What field type is the field [Duration]? is it a numeric field type?
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,226 Points
This last line should probably be
WeekdayAdd($start, $k)
But that is not the problem with the syntax.
Are all those holiday fields like [Holiday  New Years Eve] date field types? Maybe some are the wrong field type? or [Start] is not a date field type?
WeekdayAdd($start, $k)
But that is not the problem with the syntax.
Are all those holiday fields like [Holiday  New Years Eve] date field types? Maybe some are the wrong field type? or [Start] is not a date field type?
 556 Points
Start is a work date but now I have it like this:
var date thanksd =[Holiday  17Thanks];
var date thanksp =[Holiday  17Thanks2];
var date christe = [Holiday  17ChristE];
var date christd = [Holiday  17ChristD];
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = ToDate([Start]);
var number b = $a + If($thanksd >= $start and $thanksd <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($thanksp >= $start and $thanksp <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($christe >= $start and $christe <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($christd >= $start and $christd <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($newyearse >= $start and $newyearse <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($newyearsd >= $start and $newyearsd <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($easter >= $start and $easter <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($memorial >= $start and $memorial <= WeekdayAdd($start, $h), 1, 0);
var number j = $i + If($julyth >= $start and $julyth <= WeekdayAdd($start, $i), 1, 0);
var number k = $j + If($laborday >= $start and $laborday <= WeekdayAdd($start, $j), 1, 0);
var number l = $k + If($thanksgivingt >= $start and $thanksgivingt <= WeekdayAdd($start, $k), 1, 0);
var number m = $l + If($thanksgivingf >= $start and $thanksgivingf <= WeekdayAdd($start, $l), 1, 0);
var number n = $m + If($christmase >= $start and $christmase <= WeekdayAdd($start, $m), 1, 0);
var number o = $n + If($christmasd >= $start and $christmasd <= WeekdayAdd($start, $n), 1, 0);
WeekdayAdd($start, $o)
var date thanksd =[Holiday  17Thanks];
var date thanksp =[Holiday  17Thanks2];
var date christe = [Holiday  17ChristE];
var date christd = [Holiday  17ChristD];
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = ToDate([Start]);
var number b = $a + If($thanksd >= $start and $thanksd <= WeekdayAdd($start, $a), 1, 0);
var number c = $b + If($thanksp >= $start and $thanksp <= WeekdayAdd($start, $b), 1, 0);
var number d = $c + If($christe >= $start and $christe <= WeekdayAdd($start, $c), 1, 0);
var number e = $d + If($christd >= $start and $christd <= WeekdayAdd($start, $d), 1, 0);
var number f = $e + If($newyearse >= $start and $newyearse <= WeekdayAdd($start, $e), 1, 0);
var number g = $f + If($newyearsd >= $start and $newyearsd <= WeekdayAdd($start, $f), 1, 0);
var number h = $g + If($easter >= $start and $easter <= WeekdayAdd($start, $g), 1, 0);
var number i = $h + If($memorial >= $start and $memorial <= WeekdayAdd($start, $h), 1, 0);
var number j = $i + If($julyth >= $start and $julyth <= WeekdayAdd($start, $i), 1, 0);
var number k = $j + If($laborday >= $start and $laborday <= WeekdayAdd($start, $j), 1, 0);
var number l = $k + If($thanksgivingt >= $start and $thanksgivingt <= WeekdayAdd($start, $k), 1, 0);
var number m = $l + If($thanksgivingf >= $start and $thanksgivingf <= WeekdayAdd($start, $l), 1, 0);
var number n = $m + If($christmase >= $start and $christmase <= WeekdayAdd($start, $m), 1, 0);
var number o = $n + If($christmasd >= $start and $christmasd <= WeekdayAdd($start, $n), 1, 0);
WeekdayAdd($start, $o)
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,226 Points
One of these must have the mismatch.
I would have to look at you app and check every field on the left side of the equals to check the 12 fields.
Contact me via the info on my website QuickBaseCoach.com
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = [Start];
I would have to look at you app and check every field on the left side of the equals to check the 12 fields.
Contact me via the info on my website QuickBaseCoach.com
var date newyearse =[Holiday  New Years Eve];
var date newyearsd =[Holiday  New Years Day];
var date easter = [Holiday  EasterGF];
var date memorial = [Holiday  Memorial Day];
var date julyth = [Holiday  July 4];
var date laborday = [Holiday  Labor Day];
var date thanksgivingt = [Holiday  Thanksgiving T];
var date thanksgivingf = [Holiday  Thanksgiving F];
var date christmase = [Holiday  Christmas Eve];
var date christmasd = [Holiday  Christmas Day];
var number a = [Duration];
var date start = [Start];
 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
 3036 Conversations
 81 Followers