Total hours that fall between two times

[egemencoskun]

Hi All,

Need a formula to calculate total number of hours that fall between 2 specific times as below.

Formula should go to Unsociable Hours Column (F)

				From	20:00	In the evening
				To	08:00	in the morning		Ignoring the break!!!
Employee	IN	OUT	Break (mins)	Total Hours	Unsociable Hours			Uns Should be
A	04:30	11:00	60	5.50				3.5	Between 04:30 and 08:00 and the rest is normal hours
B	20:00	07:00	0	11.00				11	Between 20:00 and 07:00 so no normal time
N	19:00	07:30	30	12.00				11.5	Between 20:00 and 07:30 and the rest is normal hours
G	22:00	10:00	45	11.25				10	Between 22:00 and 08:00 and the rest is normal hours
F	23:30	12:00	50	11.67				8.5	Between 23:30 and 08:00 and the rest is normal hours
D	00:00	13:00	40	12.33				"	"
E	03:00	16:00	45	12.25				"	"
R	05:00	08:00	50	2.17				"	"
T	07:00	16:00	30	8.50				"	"
Y	08:00	19:00	45	10.25				"	"
U	09:00	15:00	60	5.00				"	"
I	08:05	16:00	60	6.92
O	07:55	07:00	45	22.33
P	09:00	06:00	50	20.17
Z	11:00	19:00	55	7.08
X	13:00	22:00	45	8.25
C	15:00	23:00	50	7.17
V	17:00	01:00	60	7.00
B	19:00	03:00	60	7.00
N	21:00	04:00	75	5.75
M	23:00	09:00	95	8.42
L	23:45	22:00	150	19.75
K	01:00	07:00	45	5.25
Q	01:25	08:00	40	5.92
W	02:00	08:05	90	4.58
E	03:00	07:55	85	3.50

<tbody>
</tbody>

I have a sample SS but cant figure how to upload.

 

[yky]

Does it make any difference if break time falls in normal hour or unsociable hour? I'd think it would but you don't specify what period the break time is in. Seems to me it doesn't matter.

 

[jorismoerings]

Hi,

assumed lunch is during sociable hours.
See if this works for you:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I
1					From	20:00	In the evening
2					To	08:00	in the morning		Ignoring the break!!!
3	Employee	IN	OUT	Break (mins)	Total Hours	Unsociable Hours			Uns Should be
4	A	04:30	11:00	60	5,50	3,50			3,5
5	B	20:00	07:00	0	11,00	11,00			11
6	N	19:00	07:30	30	12,00	11,50			11,5
7	G	22:00	10:00	45	11,25	10,00			10
8	F	23:30	12:00	50	11,67	8,50			8,5
9	D	00:00	13:00	40	12,33	8,00
10	E	03:00	16:00	45	12,25	5,00
Sheet1

Cell Formulas
Range	Formula
E4	=((C4-B4+(B4>C4)-(D4/60/24)))*24
F4	=E4-(IF(B4C4,$F$1)-MAX(B4,$F$2),MAX(0,$F$1-B4)+MAX(0,C4-$F$2))-(D4/60/24))*24

 

[kieuchau]

Thanks for posting it, the formula works on F4 until you get to an example like employee "U" .. is there anyway to get around this? Thanks!

Hi,

assumed lunch is during sociable hours.
See if this works for you:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I
1					From	20:00	In the evening
2					To	08:00	in the morning		Ignoring the break!!!
3	Employee	IN	OUT	Break (mins)	Total Hours	Unsociable Hours			Uns Should be
4	A	04:30	11:00	60	5,50	3,50			3,5
5	B	20:00	07:00	0	11,00	11,00			11
6	N	19:00	07:30	30	12,00	11,50			11,5
7	G	22:00	10:00	45	11,25	10,00			10
8	F	23:30	12:00	50	11,67	8,50			8,5
9	D	00:00	13:00	40	12,33	8,00
10	E	03:00	16:00	45	12,25	5,00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E4 =((C4-B4+(B4>C4)-(D4/60/24)))*24 F4 =E4-(IF(B4<C4,MIN(C4,$F$1)-MAX(B4,$F$2),MAX(0,$F$1-B4)+MAX(0,C4-$F$2))-(D4/60/24))*24 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jorismoerings]

Hi,

This is the result from the formula for employee U. I don't understand to what i need to get around. From my perspective the result of unsociable hours is correct. Can you explain what you mean.

Book1
	A	B	C	D	E	F
3	Employee	IN	OUT	Break (mins)	Total Hours	Unsociable Hours
4	A	04:30	11:00	60	5,5	3,5
5	B	20:00	07:00	0	11	11,0
6	N	19:00	07:30	30	12	11,5
7	G	22:00	10:00	45	11,25	10,0
8	F	23:30	12:00	50	11,67	8,5
9	D	00:00	13:00	40	12,33	8,0
10	E	03:00	16:00	45	12,25	5,0
11	R	05:00	08:00	50	2,17	3,0
12	T	07:00	16:00	30	8,5	1,0
13	Y	08:00	19:00	45	10,25	0,0
14	U	09:00	15:00	60	5	0,0
15	I	08:05	16:00	60	6,92	0,0
Sheet1

Cell Formulas
Range	Formula
F14	=E14-(IF(B14C14,$F$1)-MAX(B14,$F$2),MAX(0,$F$1-B14)+MAX(0,C14-$F$2))-(D14/60/24))*24

 

[kieuchau]

Hi Joris,
Thanks for replying back! I agree that it should return 0.0 hours for employee U.
But for some reason, when I use the formula - it gives me a negative value.

for example:
1:15 PM to 3:34 PM returns -.07