Calculate the gap between two days.

[Mr_Phil]

I'm looking for a simple way to calculate between two days. I thought it'd be simple. But, apparently days are not dates
The Gap between pulling parts and the tech pickup of his parts will be used to progressively add to the number of parts issued. THAT was actually easy to do. I feel like the answer is far more simple than I am seeing. If it help, Monday is always day one and Sunday is always day seven. I did a really gnarly nested if formula. I'd rather not copy that formula down 1000 rows of data if I can avoid it.

Capture.PNG

Any help appreciated. Thanks.

[AlKey]

Maybe this
Enter formula in D2 and copy down
Lookup table in F2:G8

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G
1	PULL DAY	DELIVERY	PICKUP	GAP		Lookup Table
2	MONDAY	TUESDAY	WEDNESDAY	2		MONDAY	1
3	TUESDAY	WEDNESDAY	THURSDAY	2		TUESDAY	2
4	WEDNESDAY	THURSDAY	THURSDAY	1		WEDNESDAY	3
5	THURSDAY	FRIDAY	SUNDAY	3		THURSDAY	4
6						FRIDAY	5
7						SATURDAY	6
8						SUNDAY	7
9

[Phuocam]

Or try:

=IFERROR(MOD(MATCH(C2,$F$2:$F$8,0)-MATCH(A2,$F$2:$F$8,0)-1,7)+1,"")

[leelnich]

If you don't want to use cells, you could just define a name to contain the list. (Click Name Manager on the Formula tab.)
DayNm = {"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}

Please Login or Register  to view this content.

NOTE: Since you're applying MOD to a difference, there's no need to re-align numbering to 0-6

[Mr_Phil]

Maybe this
Enter formula in D2 and copy down [snip] ...

Or try: =IFERROR(MOD(MATCH(C2,$F$2:$F$8,0)-MATCH(A2,$F$2:$F$8,0)-1,7)+1,"")

If you don't want to use cells, you could just define a name to contain the list....[snip]

I want to thank each of you for your help. I was sitting there knowing that there was a way to do it and it would not come to me. That is funny because I do lookups all the time and it didn't even occur to me. I was intrigued by the MOD formula because I have never used it before. But, I went with the combo of the MOD and Name. I did that because I have never used MOD before and have only used the name manager a couple of times. So, that gave me the chance to actually learn and understand the formula and the manager for future use. Thanks again.