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

#### burnthepapper

##### New Member
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### steve the fish

##### Well-known Member
Hi. You need to show us what you have. Perhaps an example with what you have and an expected result.

#### burnthepapper

##### New Member
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

#### Attachments

• Screenshot 2020-11-09 143708.png
59.6 KB · Views: 5

#### burnthepapper

##### New Member
Malfunctions (1).xlsx
BCDEFGHIJKLMNOP
2No.MachineModelSerial no.SupplierMalfunctionHours out of serviceStart of work dayEnd of work dayDate and time of malfunctionDate and time of repairTimestamp MalfunctionTimestamp Repair
3DateTimeDateTime
41Telehandler550-170106030134:10:0005:00:0001:00:0031.10.202009:2031.10.202013:3031.10.2020 09:2031.10.2020 13:30
52Boom lift1500AJP10933913###################05:00:0001:00:007.11.202013:008.11.202010:007.11.2020 13:008.11.2020 10:00
63CraneGT 9150E14:00:0005:00:0001:00:006.11.202008:306.11.202022:306.11.2020 08:306.11.2020 22:30
74Telehandler550-17010603013###################05:00:0001:00:002.11.202007:103.11.202007:002.11.2020 07:103.11.2020 07:00
85Telehandler550-170106030134:10:0005:00:0001:00:0031.10.202009:2031.10.202013:3031.10.2020 09:2031.10.2020 13:30
96###################05:00:0001:00:009.11.202015:0012.11.202017:009.11.2020 15:0012.11.2020 17:00
107###################05:00:0001:00:007.11.202014:008.11.202010:007.11.2020 14:008.11.2020 10:00
118###################05:00:0001:00:007.11.202007:008.11.202010:007.11.2020 07:008.11.2020 10:00
1290:00:0005:00:0001:00:000.1.1900 00:000.1.1900 00:00
13100:00:0005:00:0001:00:000.1.1900 00:000.1.1900 00:00
Sheet1
Cell Formulas
RangeFormula
O4:O13O4=K4+L4
P4:P13P4=M4+N4
B5:B13B5=B4+1
H12:H13,H4:H10H4=(NETWORKDAYS.INTL(O4,P4,"0000000")-1)*(J4-I4)+MOD(P4,1)-MOD(O4,1)
H11H11=(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))

Replies
1
Views
100
Replies
8
Views
281
Replies
20
Views
827
Replies
9
Views
304
Replies
3
Views
247

1,133,623
Messages
5,659,934
Members
418,538
Latest member
alc51103

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back