This week while working on a large project that involves the court system, I had to figure out how to calendar a date that is a certain number of business days before a hearing date. Knowing the hearing date, how would I calculate the unknown prior date? Ninox doesn’t have a built-in way to do this, but I thought maybe I could use the “Workdays” function to figure out my answer.
The “Workdays” function subtracts the difference between two dates, leaving out Saturdays and Sundays. It is very similar to the “Days” function, but it only counts what most of us would consider working days in the result. Here’s an example:
let xToday := today(); let xChristmas := date(2021, 12, 25); workdays(xToday, xChristmas)
This formula will yield the number of working days until Christmas.
Using this “Workdays” function, I wondered how to come up with a solution to my initial problem. I knew my mystery date had to be a certain number of business days before the hearing is scheduled to occur. Understanding that number of days would be variable, I called that number of days “Days Offset”and stored it in a table with other hearing information. Figuring I might need to simply count backward, I discovered a solution.
Here’s how I did it:
let xDate := 'Hearing Date'; let xOffset := abs('Days Offset'); let xNewDate := 'Hearing Date'; while workdays(xNewDate, xDate) < xOffset do xNewDate := xNewDate - 1 end ; xNewDate
This code block simply counts backward from the hearing date until the difference in workdays is equal to the offset days. It worked! I now have a solution to my problem that I can embed into my larger code block when creating calendar events using a button. This process reinforced my confidence that being familiar with the standard Ninox functions while applying a bit of logic can solve many initially bewildering questions.