Formula to count days

[Jendjieh]

I need help with a simple formula to calculate the difference between days.
So I have 3 columns, the first two show day abbreviations as MON, TUE, WED, THU, FRI, SAT, SUN and the third one gives you the difference between a day in column two vs column one.
Example: Col 1: MON, Col 2: THU, Col 3: 3 (as THU - MON so 4 - 1 = 3)
I made a little table on the side to tell excel that MON = 1, TUE = 2 etc. and am using vlookups to deduct the first value from the second, absolute values.
However, it's not working in case column 1 shows a "higher" day than column 2, I used absolute values to overcome this, but it does not work how I wanted.
And if I have Col 1: WED, COL 2: TUE, Col 3 shows 1, but it should be 7... (THU, FRI, SAT, SUN, MON, TUE, WED)
In short: I need to calculate the actual number of days passed between two weekdays, without specific dates attached.
Is it something possible to achieve using a formula?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	wed	tue	6		Mon
3	mon	thu	3		Tue
4					Wed
5					Thu
6					Fri
7					Sat
8					Sun
Lists

Cell Formulas
Range	Formula
C2:C3	C2	=MATCH(B2,$E$2:$E$8,0)-MATCH(A2,$E$2:$E$8,0)+IF(MATCH(B2,$E$2:$E$8,0)<MATCH(A2,$E$2:$E$8,0),7,0)

 

[Jendjieh]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	wed	tue	6		Mon
3	mon	thu	3		Tue
4					Wed
5					Thu
6					Fri
7					Sat
8					Sun
Lists

Cell Formulas
Range	Formula
C2:C3	C2	=MATCH(B2,$E$2:$E$8,0)-MATCH(A2,$E$2:$E$8,0)+IF(MATCH(B2,$E$2:$E$8,0)<MATCH(A2,$E$2:$E$8,0),7,0)

Thank you so much!

 

[FlameRetired]

Another similar to Fluff's in cell F2 and copied down. =MOD(MATCH(B2,$E$2:$E$8,0)-MATCH(A2,$E$2:$E$8,0),7)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

wed​

[/td][td]

tue​

[/td][td]

6​

[/td][td][/td][td]

Mon​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

mon​

[/td][td]

thu​

[/td][td]

3​

[/td][td][/td][td]

Tue​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Wed​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Thu​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Fri​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Sat​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Sun​

[/td][td][/td][/tr]
[/table]

 

[Jendjieh]

Another similar to Fluff's in cell F2 and copied down. =MOD(MATCH(B2,$E$2:$E$8,0)-MATCH(A2,$E$2:$E$8,0),7)

	A​	B​	C​	D​	E​	F​
1​
2​	wed​	tue​	6​		Mon​	6​
3​	mon​	thu​	3​		Tue​	3​
4​					Wed​
5​					Thu​
6​					Fri​
7​					Sat​
8​					Sun​

It works as well, thank you!
I don't understand the usage of MOD here, do you divide numbers by 7?

 

[Fluff]

Thank you so much!

You're welcome & thanks for the feedback.

 

[FlameRetired]

It works as well, thank you!
I don't understand the usage of MOD here, do you divide numbers by 7?

It returns the correct count whether you subtract the smaller from the larger or the larger from the smaller. MOD returns the remainder in a division operation. I chose to divide by 7 because there are 7 days in a week. The remainder is always correct.

I you are interested the same strategy is often employed when computing times where it is possible the start time is PM and the end time is AM. (hint: in those cases the divisor is 1 and the remainders will be decimal fractions.)

Did this help?

 

[Jendjieh]

It returns the correct count whether you subtract the smaller from the larger or the larger from the smaller. MOD returns the remainder in a division operation. I chose to divide by 7 because there are 7 days in a week. The remainder is always correct.

I you are interested the same strategy is often employed when computing times where it is possible the start time is PM and the end time is AM. (hint: in those cases the divisor is 1 and the remainders will be decimal fractions.)

Did this help?

I had to analyze a few examples for my brain to finally get it, but now I do understand how it works.
Thank you again, I love to learn new things

 

[FlameRetired]

I had to analyze a few examples for my brain to finally get it, but now I do understand how it works.
Thank you again, I love to learn new things

Me too. You're welcome. Glad to help.