Date Formula that ignores holidays, applies workday and returns rows for yesterday

Dawn McCallister

I am working on a dashboard report that returns records for "yesterday" but "yesterday should ignore Saturdays, Sundays and Holidays. I was able to construct a formula (using a helper column) that looks at the audit date and returns a "1" if the audit date is one day in the past and ignores Saturdays and Sundays (=IF([Audit Date]@row = WORKDAY(TODAY(),-1), 1,0). I am struggling to add the condition to include Holidays. Any assistance would be appreciated.

Paul Newcome

Try creating a separate sheet that has a date type column and all holiday dates going down this column. Then use a cross sheet reference to incorporate this in the third portion of the WORKDAY function.

WORKDAY(TODAY(), -1, {Reference Sheet Date Column})

Dawn McCallister

I have not succesfully resolved this issue, I am hoping that by adding a comment it will generate some discussion.

Paul Newcome

Try creating a separate sheet that has a date type column and all holiday dates going down this column. Then use a cross sheet reference to incorporate this in the third portion of the WORKDAY function.

WORKDAY(TODAY(), -1, {Reference Sheet Date Column})

Dawn McCallister

@Paul Newcome - Worked perfectly .. thank you !

Paul Newcome

Happy to help. 👍️