Before I answered this I tried the simple-minded approach by assuring that I had a reference to the Excel library and then trying to use the WORKDAY function - but no such luck. If WorkDay is in a library module, it isn't in the ones I tried.
That leaves you with the option of building a function to compute this for you. You would not put the function in the table but you could write a query to do this, or you could use the function in VBA code behind the scenes in the form that makes an entry in the table.
If you go this far, then you could also have a table of holidays and could use DLookup to determine whether a given day was in fact a holiday. The function could do the business day step-back, then test whether that day was a holiday and, if so, step back again until you find a day that is a non-holiday business day. I'm thinking in the worst case scenario, your "observed on the following Monday" holidays would give you the worst trouble (if you ran this code on a Tuesday).
How comfortable are you with VBA?
Here's the overview.
In the function, you would use DatePart("w", {starting date} ) to get the day of the week (1=Sunday, 7 = Saturday). From there, your initial step-back would be to do a DateAdd( "d", -1, {starting date} ) to determine the previous working day unless the starting date was Monday (in which case, step back 3 days) or Sunday (in which case step back 2 days).
That date is your "tentative" previous business day. Then make a table with the holidays in it and see if that day is a holiday. For example, a DCount to see if that date is in the table would do it.
In the worst case scenario, it IS - so you would start the first test over again using the (new) tentative date instead of the (original) starting date for your next step-back. I know of no cases where two holidays fall close enough that the second pass would also hit a holiday.
The reason I didn't code this up for you is that it depends on how comfortable you are with VBA and also depends on a specific structure for the DB so that you can determine the holiday dates. I don't want to make that assumption for you.
