Help with formula: determine if a date is a weekday or holiday

Ask for Help Formulas
1

I’m trying to create a formula in Grist that can automatically identify whether a given date is a weekday or a holiday. Ideally, the formula should return either a boolean value or a label like “Weekday” / “Holiday”.

I already have a Date field in my document. What I’d like is for Grist to consider:
• Saturdays and Sundays as holidays
• A list of official Italian national holidays (e.g., January 1st, April 25th, August 15th, etc.)

It would be very helpful to get a sample formula or some guidance on how to set up a calculated column to achieve this.

Thanks a lot for your support!

2

This kind of formulas are always a bit tricky, since they depend on your local holidays. And also you have to keep in mind that some holidays such as Easter (and other in various religious traditions) move between years. The best solution usually is to make an extra table that just keeps track of the holidays. The simplest version would just keep track of specific holidays, while a more advanced version can also keep track of reacuring ones. An example of the code for the simple version would be:

 if WEEKDAY($Test_day,2)>=6: #Check if Saturday or Sunday
  return True
if $Test_day in Holiday.all.Holiday: #Check for holidays
  return True
return False

and a more advanced version that takes into account repeating hoildays might be:

if WEEKDAY($Test_day,2)>=6: #Check if Saturday or Sunday
  return True
if $Test_day in Holiday.all.Holiday: #Check for holidays
  return True
repeating=Holiday.lookupRecords(Same_day_each_year=True)
testYear=$Test_day.year
for rday in repeating:
  if DATE(testYear,rday.Holiday.month,rday.Holiday.day)==$Test_day:
    return True
return False

An example grist document can be found at: https://docs.getgrist.com/8CynSXhyvNm4/Holiday-example?utm_id=share-doc

2 Likes
3

When there are multiple locations/regions to account for, a table may be the only practical approach.