Skip to main content

Hi everyone. I’m trying to write a formula that shows whether or not a date is a UK Bank Holiday. I’ve been using and IF formula that refers to a field with numbered days of the year, i.e. where January 1st = 1, December 31st = 365, for all days of 2019. So far my formula is IF({Day of year}=1, “Bank Holiday”) because New Year’s Day is a Bank Holiday, but I want to display “Bank Holiday” for every date that is a Bank Holiday i.e. a list of numbers between 1 and 365. Can I include all those numbers within the same formula, or is there another way to do this? Thanks.

You can use a SWITCH() statement here:

SWITCH(
   {Day of year},
   1, "Bank Holiday",
   37, "Bank Holiday",
   etc...
   359, "Bank Holiday"
)

What this says is,
“Look at the value of the {Day of year} field, and if its value is 1, return "Bank Holiday", if its value is 37, return "Bank Holiday", etc… and if its value doesn’t match any of the options listed, do nothing.”

You may also want to check out this excellent work by @W_Vann_Hall, as it may prove useful to you:
U.S. Holidays for WORKDAY() and WORKDAY_DIFF()

Terms & ConditionsAccessibility statement