# Number of days till next delivery

#### Maci3j

##### Board Regular
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Dave Patton

##### Well-known Member
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
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
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))

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,678
Messages
5,838,736
Members
430,566
Latest member
ChanchalSingh

### 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.

### Which adblocker are you using?

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

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