Calculate hours between two working days with defined work hours (from 5 AM to 1 AM)

[burnthepapper]

Good day,

I am sorry if this question had already been asked, I've tried searching on the forum, and while there were people with similar issues, I was unable to find someone with the exact requirements.

I am trying to make a table that calculate how long a machine on a project site was out of commission between two timestamps while taking into account work hours. The issue is that the project site is conducted in three shifts, so the place is in operation from 5 AM to 1 AM (20 hours basically), 7 days a week.

I've tried to modify a few of the formulas offered for similar questions but to no avail, doubly so because I am unable to figure out a way to take into account the fact the "work day" spills over into next day.

Additionally, if it's not too much trouble, I'd really appreciate a few pointers on how to make the table universally applicable. As in, be able to define the start of the work day, end of the work day and which days in the week are work days and which are not or are holidays.

Thank you in advance for any assistance you may be able to help me.

 

[steve the fish]

Hi. You need to show us what you have. Perhaps an example with what you have and an expected result.

 

[burnthepapper]

I apologize, here's a rough draft of what the table is like with the formulas + screenshot. I wasn't really able to figure out how to post my table.

Machine	Total hours	Workday start	Workday end	Malfunction date	Malfunction time	Repair date	Repair time	Malfunction timestamp	Repair timestamp
Forklift	=(NETWORKDAYS.INTL(O4;P4;"0000000")-1)*(J4-I4)+MOD(P4;1)-MOD(O4;1)	05:00	01:00					Malf. date + time	Repair date + time

 

[burnthepapper]

Malfunctions (1).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	No.	Machine	Model	Serial no.	Supplier	Malfunction	Hours out of service	Start of work day	End of work day	Date and time of malfunction		Date and time of repair		Timestamp Malfunction	Timestamp Repair
3										Date	Time	Date	Time
4	1	Telehandler	550-170	10603013			4:10:00	05:00:00	01:00:00	31.10.2020	09:20	31.10.2020	13:30	31.10.2020 09:20	31.10.2020 13:30
5	2	Boom lift	1500AJP	10933913			###################	05:00:00	01:00:00	7.11.2020	13:00	8.11.2020	10:00	7.11.2020 13:00	8.11.2020 10:00
6	3	Crane	GT 9150E				14:00:00	05:00:00	01:00:00	6.11.2020	08:30	6.11.2020	22:30	6.11.2020 08:30	6.11.2020 22:30
7	4	Telehandler	550-170	10603013			###################	05:00:00	01:00:00	2.11.2020	07:10	3.11.2020	07:00	2.11.2020 07:10	3.11.2020 07:00
8	5	Telehandler	550-170	10603013			4:10:00	05:00:00	01:00:00	31.10.2020	09:20	31.10.2020	13:30	31.10.2020 09:20	31.10.2020 13:30
9	6						###################	05:00:00	01:00:00	9.11.2020	15:00	12.11.2020	17:00	9.11.2020 15:00	12.11.2020 17:00
10	7						###################	05:00:00	01:00:00	7.11.2020	14:00	8.11.2020	10:00	7.11.2020 14:00	8.11.2020 10:00
11	8						###################	05:00:00	01:00:00	7.11.2020	07:00	8.11.2020	10:00	7.11.2020 07:00	8.11.2020 10:00
12	9						0:00:00	05:00:00	01:00:00					0.1.1900 00:00	0.1.1900 00:00
13	10						0:00:00	05:00:00	01:00:00					0.1.1900 00:00	0.1.1900 00:00
Sheet1

Cell Formulas
Range	Formula
O4:O13	O4	=K4+L4
P4:P13	P4	=M4+N4
B5:B13	B5	=B4+1
H12:H13,H4:H10	H4	=(NETWORKDAYS.INTL(O4,P4,"0000000")-1)*(J4-I4)+MOD(P4,1)-MOD(O4,1)
H11	H11	=(NETWORKDAYS(O11,P11)*20)-IF(WEEKDAY(O11,2)>=6,0,MIN(MAX(24*(MOD(O11,1)-O11),0),20))-IF(WEEKDAY(P11,2)>=6,MIN(24*MAX((O11-MOD(P11,1)),0),20))