Forum Discussion
AlexCertificati
6 years agoQrew Cadet
To back up a step to the question in the title: You can use simple binary operators against date values. A simple example would be
[Weekdays Month To Date]
var number holidays = if(FirstDayOfMonth(Today()) < [Independence Day] and [Independence Day] < Today(),1,0)
WeekdaySub((Today()+ Days(1)),(LastDayOfMonth(AdjustMonth(Today(),-1))))-$holidays
This strikes me as one of those situations where we can compromise theoretical scalability a bit given our known real-world constraints. Are there any months with multiple federal holidays? In other words it looks like it would be clunky to expand an expression like this to handle more holidays, but since we know there aren't that many of them, and they're fixed over the course of a year, a direct approach like this might be best.
So the expanded expression looks like
var number holidays =
if(FirstDayOfMonth(Today()) < [holiday1] and [holiday1] < Today(),1,0)+
if(FirstDayOfMonth(Today()) < [holiday2] and [holiday2] < Today(),1,0)+
if(FirstDayOfMonth(Today()) < [holiday3] and [holiday3] < Today(),1,0)+
etc through 13 or however many you have.
You'd never write a formula like that if you had 100 holidays, or if there were a variable number, but it's probably fine for this application.
Here's more information on formula variables, in case that's unfamiliar to you: https://help.quickbase.com/user-assistance/formula_variables.html
[Weekdays Month To Date]
var number holidays = if(FirstDayOfMonth(Today()) < [Independence Day] and [Independence Day] < Today(),1,0)
WeekdaySub((Today()+ Days(1)),(LastDayOfMonth(AdjustMonth(Today(),-1))))-$holidays
This strikes me as one of those situations where we can compromise theoretical scalability a bit given our known real-world constraints. Are there any months with multiple federal holidays? In other words it looks like it would be clunky to expand an expression like this to handle more holidays, but since we know there aren't that many of them, and they're fixed over the course of a year, a direct approach like this might be best.
So the expanded expression looks like
var number holidays =
if(FirstDayOfMonth(Today()) < [holiday1] and [holiday1] < Today(),1,0)+
if(FirstDayOfMonth(Today()) < [holiday2] and [holiday2] < Today(),1,0)+
if(FirstDayOfMonth(Today()) < [holiday3] and [holiday3] < Today(),1,0)+
etc through 13 or however many you have.
You'd never write a formula like that if you had 100 holidays, or if there were a variable number, but it's probably fine for this application.
Here's more information on formula variables, in case that's unfamiliar to you: https://help.quickbase.com/user-assistance/formula_variables.html