Forum Discussion
Help calculating a date, excluding weekends and US holidays
Hello - I am trying to fix an old IF statement to show a business day Work day. The current IF statement "=IF(WEEKDAY(NOW())=2,NOW()-3,NOW()-1))" still works except for US holidays, so we had a calendar popup to choose the correct date when there was a US holiday. The calendar popup doesn't work now due to an upgrade of Microsoft 365 to 64-bit. I tried updating the IF statement to a Workday IF statement and used a table "Holidays" listing the US holidays, but it isn't working. Any thoughts?
1 Reply
the formula should look like:
=WORKDAY(NOW(), -1, HOLIDAY)
where HOLIDAY should be a named range with a list of all dates to be considered as a holiday. Although it doesn't have to be a defined named, it could just be a list of dates in line using a defined name is much easier to support. I have created a table that easily adds/includes various holidays. I will try to attach a sheet with it as an example.
