Add Time Overlap

[hajiali]

Hello All Im trying to find a way to calculate time overlaps.

ex;

If Agent Actual shift is 06:30-15:00 and agents wants to pickup another shift from14:00-22:30 excel calculates 1 hour over lap vise visa
or
If Agent Actual shift is 14:00-22:30 and agents wants to pickup another shift from 06:30-15:00 excel calculates 1 hour over lap

I can have start and end times of shift in one cell or I can also split the start and end times to 2 separate cells whichever would be easiest to calculate time overlap. I would like a non-array formula or VBA any suggestions is greatly appreciated. Let me know if I need to clarify anything.

 

[Crystalyzer]

Try this

Solutions 20210719.003.xlsm
	A	B	C	D	E	F
1		Shift 1		Shift 2
2		Start time	End Time	Start time	End Time	Shift Overlap
3	Agent A	14:00	22:30			1:00
4	Agent A			6:30	15:00	1:00
5	Agent B	12:00	17:00			2:00
6	Agent B			8:00	14:00	2:00
7	Agent C					0:00
Time Overlap

Cell Formulas
Range	Formula
F3:F7	F3	=IF(SUMPRODUCT((($A$3:$A$7=A3)*($E$3:$E$7))-(($A$3:$A$7=A3)*($B$3:$B$7)))>=0,SUMPRODUCT((($A$3:$A$7=A3)*($E$3:$E$7))-(($A$3:$A$7=A3)*($B$3:$B$7))),0)

 

[hajiali]

thank you Crystalyzer for the reply Below is the scenario that I would have. I would need to check each row for the overlap as its for the same agent the actual shift and pickup shift. How would I get the same results. if shift does not have an overlap put the word GAP for that column. Hope this clarifies.

Shift 1		Shift 2
Start time	End Time	Start time	End Time	Shift Overlap
14:00	22:30	6:30	15:00	1:00
8:00	14:00	12:00	17:00	2:00
8:00	14:00	14:00	21:30	0:00
14:00	22:30	4:00	15:00	GAP

 

[Crystalyzer]

that simplifies things considerably

Solutions 20210719.003.xlsm
	A	B	C	D	E	F
1		Shift 1		Shift 2
2		Start time	End Time	Start time	End Time	Shift Overlap
3	Agent A	6:30	15:00	14:00	22:30	1:00
4	Agent B	12:00	17:00			No Shift 2
5	Agent C	8:00	12:00	13:00	17:00	GAP
Time Overlap

Cell Formulas
Range	Formula
F3:F5	F3	=IFS(ISBLANK(D3),"No Shift 2",C3-D3>0,C3-D3,TRUE,"GAP")

 

[hajiali]

my version if Excel does not have the "ifs" function.

 

[Crystalyzer]

Solutions 20210719.003.xlsm
	A	B	C	D	E	F	G
1		Shift 1		Shift 2
2		Start time	End Time	Start time	End Time	Shift Overlap IFS	Shift Overlap IF
3	Agent A	6:30	15:00	14:00	22:30	1:00	1:00
4	Agent B	12:00	17:00			No Shift 2	No Shift 2
5	Agent C	8:00	12:00	13:00	17:00	GAP	GAP
Time Overlap

Cell Formulas
Range	Formula
F3:F5	F3	=IFS(ISBLANK(D3),"No Shift 2",C3-D3>0,C3-D3,TRUE,"GAP")
G3:G5	G3	=IF(ISBLANK(D3),"No Shift 2",IF(C3-D3>0,C3-D3,"GAP"))

 

[hajiali]

Thank you so much for your hlep Crystalyzer based on what you gave me I was able to come up the the following Formula to do what I need. I had to write down all different scenarios to get it.

=IFERROR(IF(B1="OFF",0,IF(AND(B1>D1,B1-E1>0),0,IF(AND(B1<D1,D1-C1>0),0,IF(AND(D1<B1,B1<E1),E1-B1,IF(AND(B1<D1,D1<C1),C1-D1,0))))),0)

 

[Crystalyzer]

you marked yourself as the solution? LOL

 

[hajiali]

OOOPS LOL sorry Click on the wrong check box. thanks for the heads up.