Calculating a Date, Returning a previous date if Weekend

[sarahmoa]

I've tried to find a formula, but I'm having trouble making any of them work for me.

I'd like to try to calculate a date, but roll to the previous Friday if it falls on a weekend.

B3 - Date
C3 - Due x number of days before B3
D3 - Mailing Days (this would also be subtracted from the number of days.
E3 - Due Date
My formula is in column E:
=B3-C3-D3 will calculate the due date, but I'd like it to roll to the previous Friday if the date falls on the weekend.

I've also seen other formulas account for holidays as well - does this have to be a separate spreadsheet for reference, or is there anything native in Excel?

 

[Peter_SSs]

Put your list of holidays in the sheet (or could be another sheet), I've used range H2:H10 in the current sheet.

Then in E3 try

=WORKDAY(B3-C3-D3+1,-1,$H$2:$H$10)

 

[AliGW]

Look at the following functions in Excel:

WORKDAY
WORKDAY.INTL

NETWORKDAYS
NETWORKDAYS.INTL