Newbie looking for a formula

[spranger24]

I have column A and column B showing the days worked for agents and supervisors. I'd like to have a formula that shows the number of matching days between the two in column C. In the example below A2 and B2 have 3 days that they are both working.

Or

If anyone knows of a way to calculate the total hours scheduled together. Any help is much appreciated.

 

[Snakehips]

@spranger24
Might this help with the first?

MRXLMAY21.xlsm
	A	B	C
1	Agent	Supervisor	Match
2	FY::TWR	FYSMT::	3
3	F::MTWR	FYSMT::	3
4	FY:MTW:	::SMTWR	3
5	F::MTWR	::SMTWR	4
6	::SMTWR	::SMTWR	5
7	F:SM:WR	::SMTWR	4
8	:Y::TWR	FYSMT::	2
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SUMPRODUCT(ISNUMBER(FIND(MID(SUBSTITUTE(A2,":","!"),{1,2,3,4,5,6,7},1),B2,1))*1)

 

[spranger24]

@spranger24
Might this help with the first?

MRXLMAY21.xlsm
	A	B	C
1	Agent	Supervisor	Match
2	FY::TWR	FYSMT::	3
3	F::MTWR	FYSMT::	3
4	FY:MTW:	::SMTWR	3
5	F::MTWR	::SMTWR	4
6	::SMTWR	::SMTWR	5
7	F:SM:WR	::SMTWR	4
8	:Y::TWR	FYSMT::	2
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SUMPRODUCT(ISNUMBER(FIND(MID(SUBSTITUTE(A2,":","!"),{1,2,3,4,5,6,7},1),B2,1))*1)

I think that's on the right path. That formula seems to be counting more than what it should. Cell C2 should have a value of 3, where the formula gives a value of 5.

 

[Snakehips]

Are you using the = as a blank? You need to use a : instead as per my example.

Also, this is maybe getting towards a solution for your second ask.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G	H	I	J	K
10	Agent Hrs	Agent Days	Supervisor Hrs	Supervisor Days	Total Hours Togther		LEFTA	LEFTC	RIGHTA	RIGHTC	Hours Overlap
11	09:00 - 18:00	FY::TWR	10:00 - 19:00	FY::TWR	40		9	10	18	19	8
12	14:00 - 23:00	F:SM:WR	14:00 - 23:00	F:SM:WR	45		14	14	23	23	9
13	14:00 - 01:00	FY:MT::	14:00 - 23:00	FY:MT::	36		14	14	25	23	9
14	14:00 - 23:00	FY:MT::	14:00 - 01:00	FY:MT::	36		14	14	23	25	9
15	22:00 - 01:00	FY::TWR	23:00 - 02:00	FY::TWR	10		22	23	25	26	2
Sheet1

Cell Formulas
Range	Formula
G11:G15	G11	=HOUR(LEFT(A11,5))
H11:H15	H11	=HOUR(LEFT(C11,5))
I11:I15	I11	=IF(LEFT(A11,5)>RIGHT(A11,5),HOUR(RIGHT(A11,5))+24,HOUR(RIGHT(A11,5)))
J11:J15	J11	=IF(LEFT(C11,5)>RIGHT(C11,5),HOUR(RIGHT(C11,5))+24,HOUR(RIGHT(C11,5)))
K11:K15	K11	=MIN(I11:J11)-MAX(G11,H11)
E11:E15	E11	=SUMPRODUCT(ISNUMBER(FIND(MID(SUBSTITUTE(B11,":","!"),{1,2,3,4,5,6,7},1),D11,1))*K11)

I have utilised helper columns to avoid getting silly long formula.

If there are any errors due to the issue of working to, from or across midnight then I will let you work that out.

 

[spranger24]

Are you using the = as a blank? You need to use a : instead as per my example.

Also, this is maybe getting towards a solution for your second ask.

MRXLMAY21.xlsm
	A	B	C	D	E	F	G	H	I	J	K
10	Agent Hrs	Agent Days	Supervisor Hrs	Supervisor Days	Total Hours Togther		LEFTA	LEFTC	RIGHTA	RIGHTC	Hours Overlap
11	09:00 - 18:00	FY::TWR	10:00 - 19:00	FY::TWR	40		9	10	18	19	8
12	14:00 - 23:00	F:SM:WR	14:00 - 23:00	F:SM:WR	45		14	14	23	23	9
13	14:00 - 01:00	FY:MT::	14:00 - 23:00	FY:MT::	36		14	14	25	23	9
14	14:00 - 23:00	FY:MT::	14:00 - 01:00	FY:MT::	36		14	14	23	25	9
15	22:00 - 01:00	FY::TWR	23:00 - 02:00	FY::TWR	10		22	23	25	26	2
Sheet1

Cell Formulas
Range	Formula
G11:G15	G11	=HOUR(LEFT(A11,5))
H11:H15	H11	=HOUR(LEFT(C11,5))
I11:I15	I11	=IF(LEFT(A11,5)>RIGHT(A11,5),HOUR(RIGHT(A11,5))+24,HOUR(RIGHT(A11,5)))
J11:J15	J11	=IF(LEFT(C11,5)>RIGHT(C11,5),HOUR(RIGHT(C11,5))+24,HOUR(RIGHT(C11,5)))
K11:K15	K11	=MIN(I11:J11)-MAX(G11,H11)
E11:E15	E11	=SUMPRODUCT(ISNUMBER(FIND(MID(SUBSTITUTE(B11,":","!"),{1,2,3,4,5,6,7},1),D11,1))*K11)

I have utilised helper columns to avoid getting silly long formula.

If there are any errors due to the issue of working to, from or across midnight then I will let you work that out.

Much appreciated Snakehips!!