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

pjangie

New Member
Joined
Feb 26, 2019
Messages
3
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top