Number of days till next delivery

[Maci3j]

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.

 

[Dave Patton]

T202007a.xlsm
	A	B	C	D	E	F	G
2	Day	Delivery Date	Days to Next Delivery
3	Friday	0	4		Monday	0	3
4					Tuesday	1	2
5	Saturday	0	3		Wednesday	0	6
6					Thursday	1	5
7					Friday	0	4
8					Saturday	0	3
9					Sunday	0	2
1e

Cell Formulas
Range	Formula
B6:B9,B3:B4	B3	=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,2,0))
C6:C9,C3:C4	C3	=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,3,0))
B5	B5	=IF(A5="","",VLOOKUP(A5,rL,2,0))
C5	C5	=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]

T202007a.xlsm
	A	B	C	D	E	F	G
2	Day	Delivery Date	Days to Next Delivery
3	Friday	0	4		Monday	0	3
4					Tuesday	1	2
5	Saturday	0	3		Wednesday	0	6
6					Thursday	1	5
7					Friday	0	4
8					Saturday	0	3
9					Sunday	0	2
1e

Cell Formulas
Range	Formula
B6:B9,B3:B4	B3	=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,2,0))
C6:C9,C3:C4	C3	=IF(A3="","",VLOOKUP(A3,$E$3:$G$9,3,0))
B5	B5	=IF(A5="","",VLOOKUP(A5,rL,2,0))
C5	C5	=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]

You can try and test the following

T202007a.xlsm
	A	B	C
2	Date	Delivery Date	Days to Next Delivery
3	Mon 06-Jul-20	Thu 09-Jul-20	3
4	Tue 07-Jul-20	Thu 09-Jul-20	2
5	Wed 08-Jul-20	Tue 14-Jul-20	6
6	Thu 09-Jul-20	Tue 14-Jul-20	5
7	Fri 10-Jul-20	Tue 14-Jul-20	4
8
1ee

Cell Formulas
Range	Formula
B3:B7	B3	=IF(WEEKDAY(A3,2)<3,CEILING(A3-7,7)+5,CEILING(A3,7)+3)
C3:C7	C3	=B3-A3
A4:A7	A4	=A3+1

 

[Dave Patton]

Another version

T202007a.xlsm
	A	B	C
9	Mon 06-Jul-20	Thu 09-Jul-20	3
10	Tue 07-Jul-20	Thu 09-Jul-20	2
11	Wed 08-Jul-20	Tue 14-Jul-20	6
12	Thu 09-Jul-20	Tue 14-Jul-20	5
13	Fri 10-Jul-20	Tue 14-Jul-20	4
1ee

Cell Formulas
Range	Formula
B9:B13	B9	=IF(WEEKDAY(A9,2)<3,A9+4-WEEKDAY(A9,2),A9+9-WEEKDAY(A9,2))
C9:C13	C9	=B9-A9

 

[Dave Patton]

T202007a.xlsm
	A	B	C	D
9	05-Jul-20			2
10	06-Jul-20			3
11	07-Jul-20			2
12	08-Jul-20			6
13	09-Jul-20			5
14	10-Jul-20			4
15	11-Jul-20			3
1ee

Cell Formulas
Range	Formula
D9:D15	D9	=IF(WEEKDAY(A9,2)<3,4-WEEKDAY(A9,2),9-WEEKDAY(A9,2))