Workday is not omitting the holidays?

Pauline J

I have tested this manually, as well as outside of my formulas. I want to reference a date, and then search on the Holidays range (a list of holidays) and return the previous workday if the date is found in the holiday list.

The formula ALWAYS returns the day before the date, whether it's in the holiday list or not. Any help is much appreciated; I have tried variations, tried using CONTAINS, but I cannot get the results I need. I copied all the holiday dates into a date field into the same sheet, and AI suggested using

=IF(CONTAINS([Due Date]@row , [Holidays]:[Holidays]), [Due Date]@row + 1, [Due Date]@row )

But nothing is working…..

My structure: List of holidays is in a sheet called Holidays (and yes, I'm showing 2024, but that doesn't matter)

Column Formula in CalcDueDt-Hol:
=WORKDAY([Due Date]@row , -1, {Holidays Range 1})

In the top row, below, the result should be the working day before 2/19/2024, because that date is in the holiday list. But it returns the previous working day for ALL Due Dates; the rows in white are NOT in the holiday list.

Gia Thinh

Hello @Pauline J

I tried to put the HolidayDt into the same sheet to easy simulate your use case, as shown below.

Try the formula below for the CalcDueDt-Hol:

=IFERROR(WORKDAY([Due Date]@row , IF(HAS(HolidayDt:HolidayDt, [Due Date]@row ), -1, 0), HolidayDt:HolidayDt), "")

The highlighted IF() returns (-1) if the Due Date found in the HolidayDt, else returns (0). This will help to return the previous workday if the Due Date is found in the holiday list, else keep the same Due Date.

You can change the HolidayDt range in the above formula if you put it in another sheet, as below:

=IFERROR(WORKDAY([Due Date]@row , IF(HAS({Holidays Range 1}, [Due Date]@row ), -1, 0), {Holidays Range 1}), "")

Hope this works for you.