Calculating Dates to End on Friday or Monday if Original Date Ends on a Weekend

[pjangie]

Not sure it it's possible, but is there a formula that will take my date formula of subtracting/adding days to a date (=SUM(B6-65)) a step further to move the date to end on a Friday if the original calculated date ends on a Saturday and move to Monday if it originally calculates on a Sunday. I do a lot of deadline calendaring of taking a date and subtracting or adding a number of days to it and using the end date as my deadline...however, I have to manually change the date to a workday if it lands on a weekend. I have tried using the workday function, but it doesn't work the way I need it (not with my limited knowledge of it anyway). Hopefully this is enough info.

Thanks!

 

[Tetra201]

Assuming you have a date in cell A1 and a number of days to add (positive or negative) in cell B1, try the following formula:

=A1+B1+TRUNC((4-WEEKDAY(A1,17-MOD(B1,7)))/3)

 

[Tetra201]

Another approach:

=A1+B1+LOOKUP(WEEKDAY(A1+B1,16),{1,2,3},{-1,1,0})