Round back to nearest Tuesday or Friday

WiscCard

New Member
I'm trying to calculate a departure date for a shipping spreadsheet. Basically we take the delivery date, subtract 5 business days, then round backwards to the previous Friday or Tuesday.
I have a formula that is working if the delivery date falls on a Tuesday or Friday. If it doesn't, it is not rounding backward properly. Here is a table of the delivery dates and the date I need excel to calculate.
I am currently using the formula "=WORKDAY.INTL(A1,-5,1,1011011)"

DLV DATE DLV DAY DEPARTURE DATE DEPARTURE DAY
02/12/2016 Fri 25/11/2016 Fri
05/12/2016 Mon 25/11/2016 Fri
06/12/2016 Tue 29/11/2016 Tue
12/12/2016 Mon 02/12/2016 Fri
13/12/2016 Tue 06/12/2016 Tue
13/12/2016 Tue 06/12/2016 Tue
15/12/2016 Thu 06/12/2016 Tue
15/12/2016 Thu 06/12/2016 Tue
15/12/2016 Thu 06/12/2016 Tue
16/12/2016 Fri 09/12/2016 Fri
16/12/2016 Fri 09/12/2016 Fri
16/12/2016 Fri 09/12/2016 Fri
20/12/2016 Tue 13/12/2016 Tue
20/12/2016 Tue 13/12/2016 Tue
20/12/2016 Tue 13/12/2016 Tue
20/12/2016 Tue 13/12/2016 Tue
10/01/2017 Tue 03/01/2017 Tue
17/01/2017 Tue 10/01/2017 Tue
17/01/2017 Tue 10/01/2017 Tue
17/01/2017 Tue 10/01/2017 Tue
20/01/2017 Fri 13/01/2017 Fri
31/01/2017 Tue 24/01/2017 Tue
31/01/2017 Tue 24/01/2017 Tue
14/02/2017 Tue 07/02/2017 Tue

Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Special-K99

Well-known Member
Cant you just do this?
Given a date A1 then the previous Friday or Tuesday would be

=A1-LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,3,7,1,2,7,1})

Last edited:

WiscCard

New Member
Cant you just do this?
Given a date A1 then the previous Friday or Tuesday would be

=A1-LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,3,7,1,2,7,1})

Thanks, that does work once I modify slightly to account for the 5 business days of transit.
=A1-7-LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,3,0,1,2,0,1})

WiscCard

New Member
Hi Special-K99,
Can you please help me understand the last part of your formula? {1,2,3,4,5,6,7} is Sunday to Saturday I believe, but then the last part? This will help me do this myself if I have to edit. Thank you.

Replies
3
Views
323
Replies
4
Views
276
Replies
6
Views
177
Replies
0
Views
393
Replies
2
Views
115

1,191,483
Messages
5,986,845
Members
440,053
Latest member
jhollingworth

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?

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

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