Try this...

=WORKDAY(TODAY(),-1)

To exclude holiday dates...

List the holiday dates in a range of cells like A2:A15. Then:

=WORKDAY(TODAY(),-1,A2:A15)

Format the cell as Date

Note that the WORKDAY function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the WORKDAY function. It'll tell you
how to fix the problem.