Calculate total hours from two entries, with in work hours, skipping weekends.

[camthekitchenguy]

Recently found this forum after googling for different how-to's.
Came across different tutorials that were a little over my head which includes NetworkDays.

I am trying to have 2 date entries with time and date formatted like below, that will add together the hours worked within a working hours range of 7am till 3:30pm, skipping weekends.

4/18/22 7:00 AM

4/20/22 3:30 PM

I have come across other threads that are solving similar problems but was unable to adapt it properly
Any help, or direction to the correct thread, is appreciated.
Thanks

 

[camthekitchenguy]

Production Tracking.xlsx
	I	J	K	L	M	N	O	P	Q	R	S
1	Production
2	Saw	CNC	Edgeband	Dowling	Picking	Doors + Drawer Box	Assembly	Parts Wrapped	Recuts	Complete	Days
3
4
5
20
21
22
23
24
25	4/18/22 7:00 AM	4/18/22 7:00 AM	4/18/22 7:00 AM	4/18/22 8:30 AM	4/19/22 7:00 AM	4/20/22 7:30 AM	4/19/22 2:15 AM
26	4/19/22 3:30 PM	4/19/22 12:30 PM	4/20/22 3:30 PM	4/20/22 3:30 PM	4/20/22 3:30 PM	4/20/22 3:30 PM
27
Production 2022

 

[JamesCanale]

I put the formula in row 27. Below that is the thing broken up a bit so it might be easier to understand.

MrExcelPlayground8.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
1		Production
2		Saw	CNC	Edgeband	Dowling	Picking	Doors + Drawer Box	Assembly	Parts Wrapped	Recuts	Complete	Days
3
4
5
20
21
22
23
24	Workday
25	7:00:00 AM	Mon 4/18/22 7:00 AM	Mon 4/18/22 7:00 AM	Mon 4/18/22 7:00 AM	Mon 4/18/22 8:30 AM	Tue 4/19/22 7:00 AM	Wed 4/20/22 7:30 AM	Tue 4/19/22 2:15 AM
26	3:30:00 PM	Tue 4/19/22 3:30 PM	Tue 4/19/22 12:30 PM	Wed 4/20/22 3:30 PM	Wed 4/20/22 3:30 PM	Wed 4/20/22 3:30 PM	Tue 4/26/22 12:30 PM	Tue 4/19/22 3:15 PM
27		17.00	14.00	25.50	24.00	17.00	39.00	8.25
28		17	17	25.5	25.5	17	42.5	8.5
29		0.00	0.00	0.00	1.50	0.00	0.50	0.00
30		0.00	3.00	0.00	0.00	0.00	3.00	0.25
31		17.00	14.00	25.50	24.00	17.00	39.00	8.25
Sheet9

Cell Formulas
Range	Formula
I27:O27	I27	=NETWORKDAYS(I25,I26)*($H$26-$H$25)*24-MAX(MOD(I25,1)-$H$25,0)*24-MAX($H$26-MOD(I26,1),0)*24
I28:O28	I28	=NETWORKDAYS(I25,I26)*($H$26-$H$25)*24
I29:O29	I29	=MAX(MOD(I25,1)-$H$25,0)*24
I30:O30	I30	=MAX($H$26-MOD(I26,1),0)*24
I31:O31	I31	=I28-I29-I30

 

[kvsrinivasamurthy]

N4=Start Day & Time
O4=End Day & Time
S3=7:00:00
Z3=15:30:00

=IF(AND($N4>0,$O4>0),IF(INT($N4)-INT($O4)=0,MAX(0,MIN($Z$3,ROUND(MOD($O4,1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($Z$3,ROUND(MOD($O4,1),6))-$S$3)+($Z$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT($O4)-INT($N4)>1)*($Z$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT($O4)-1)),IF(INT($N4)-INT(NOW())=0,MAX(0,MIN($Z$3,ROUND(MOD(NOW(),1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($Z$3,ROUND(MOD(NOW(),1),6))-$S$3)+($Z$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT(NOW())-INT($N4)>1)*($Z$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT(NOW())-1)))

 

[camthekitchenguy]

Thank you JamesCanale & kvsrinivasamurthy for the responses.
Appreciate it!

 

[camthekitchenguy]

I'm realizing I didn't think about omitting 30min a day for unpaid lunch. I could just adjust the end time by 30min to account; which I am prepared to do.

Is there a way to deduct 30min per day within the range to account for this? If that's possible can the time in the day be determined so that it is taken into account based on the calculation when referencing the start & end time? For example, removing 11:30am to 12:00mm?

 

[kvsrinivasamurthy]

Person shift working Hrs: Start=N4, End= O4
Working Hrs: Start=S3 (7:00), End= T3(15:30)
Break Hrs: Start=V3(11:30), End=W3(12:00)

=IF(AND($N4>0,$O4>0),IF(INT($N4)-INT($O4)=0,(WEEKDAY($N4,2)<6)*(MAX(0,MIN($V$3,MOD($O4,1))-MAX(MOD($N4,1),$S$3))+MAX(0,MIN($T$3,MOD($O4,1))-MAX($W$3,MOD($N4,1)))),(WEEKDAY($N4,2)<6)*(MAX(0,$T$3-MAX($W$3,MOD($N4,1)))+MAX(0,$V$3-MAX($S$3,MOD($N4,1))))+(WEEKDAY($O4,2)<6)*(MAX(0,MIN($V$3,MOD($O4,1))-$S$3)+MAX(0,MIN($T$3,MOD($O4,1))-$W$3))+IF(INT($O4)-INT($N4)>1,($T$3-$W$3+$V$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT($O4)-1),0)),"")

 

[Sulprobil]

I suggest to look at this thread:

Shifts and bonuses

Hi, I have a problem I have been trying to find a solution to for a while now. We have people that work shifts. Bonuses can be received for each shift depending on time and day. ---**disclaimer** this is not the actual bonus system, since we don't even use dollars in my country but it's just...

www.mrexcel.com

One additional functionality might be a break, but the next can be a bank holiday, and for this a good (maybe even best) practice is a function approach which encapsulates all of it.