Forum Discussion
ArchiveUser
8 years agoQrew Captain
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 12-24-18 and Holiday 2 will be 12-25-18 (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 3-4 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 12-24-18 and Holiday 2 will be 12-25-18 (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 3-4 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.