In the attached, formula in cell B4:
=WORKDAY(A4,-1,Holidays)
You could have more control with
=WORKDAY.INTL(A4,-1,1,Holidays)
in cell B4 if WORKDAY.INTL is available to you in your version of Excel.
Holidays is the name of a table containing a list of holiday dates.
For demonstration purposes, in the attached:
The date in cell A4 is not =TODAY() but fixed to today's date (22/Nov/2024) but this will still work if you do use =TODAY()
Also, I've added a few more dates below cell A4 and used the same formulae in columns B & C. I've added conditional formatting to column A to highlight holiday dates and weekends so that you can confirm the formulae give the correct results.
2024-11-22_162713.jpg