Number of days till next delivery

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
69
Office Version
  1. 365
Hello,

I would like to ask you to help me with a formula.

Goal: how to calculate how many calendar days are left till next delivery day? The formula needs to calculate the number of calendar days till delivery even when delivery days would change (column C on the picture)
Info: Our supplier delivers only on Tuesdays and Thursdays. (column B on the picture). In the picture you will see that: 0 = no delivery. 1 = delivery.
Remarks:
- If we order on Monday, the delivery doesn't take place on Tuesday. The next possible delivery date is Thursday.
- If we order on Tuesday, the delivery doesn't take place on Tuesday. The next possible delivery date is Thursday.
- If we order on Wednesday, the delivery doesn't take place on Thursday. The next possible delivery date is Tuesday next week.
- If we order on Thursday, the delivery doesn't take place on Thursday. The next possible delivery date is Tuesday next week.
- The calculations starts on the next day after order and counts till the day and includes the day of the order. For example: Ordered on Monday and next delivery day is Thursday, then: Monday = 0 days, Tuesday = 1 day, Wednesday = 1 day, Thursday = 1 day. Total days till next delivery = 3.


Capture.PNG
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,632
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202007a.xlsm
ABCDEFG
2DayDelivery DateDays to Next Delivery
3Friday04Monday03
4  Tuesday12
5Saturday03Wednesday06
6  Thursday15
7  Friday04
8  Saturday03
9  Sunday02
1e
Cell Formulas
RangeFormula
B6:B9,B3:B4B3=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,2,0))
C6:C9,C3:C4C3=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,3,0))
B5B5=IF(A5="","",VLOOKUP(A5,rL,2,0))
C5C5=IF(A5="","",VLOOKUP(A5,rL,3,0))


The 2 formulas are the same; the first has the Lookup Table on the sheet and the second has the Lookup Table in a Named Array.
 

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
69
Office Version
  1. 365
T202007a.xlsm
ABCDEFG
2DayDelivery DateDays to Next Delivery
3Friday04Monday03
4  Tuesday12
5Saturday03Wednesday06
6  Thursday15
7  Friday04
8  Saturday03
9  Sunday02
1e
Cell Formulas
RangeFormula
B6:B9,B3:B4B3=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,2,0))
C6:C9,C3:C4C3=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,3,0))
B5B5=IF(A5="","",VLOOKUP(A5,rL,2,0))
C5C5=IF(A5="","",VLOOKUP(A5,rL,3,0))


The 2 formulas are the same; the first has the Lookup Table on the sheet and the second has the Lookup Table in a Named Array.

thanks for your contribution. However that's not exactly what I'm looking for.
In your formula you have to determine the delivery days and then count the number of days till next delivery yourself + VLOOKUP function.
My purpose is to find a formula to calculate the number of days till next delivery via formula
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,632
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202007a.xlsm
ABCD
905-Jul-202
1006-Jul-203
1107-Jul-202
1208-Jul-206
1309-Jul-205
1410-Jul-204
1511-Jul-203
1ee
Cell Formulas
RangeFormula
D9:D15D9=IF(WEEKDAY(A9,2)<3,4-WEEKDAY(A9,2),9-WEEKDAY(A9,2))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,308
Members
416,239
Latest member
Counselor85027

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
Top