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

burnthepapper

New Member
Joined
Nov 9, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Oct 20, 2009
Messages
8,423
Office Version
  1. 365
Platform
  1. Windows
Hi. You need to show us what you have. Perhaps an example with what you have and an expected result.
 

burnthepapper

New Member
Joined
Nov 9, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

MachineTotal hoursWorkday startWorkday endMalfunction dateMalfunction timeRepair dateRepair timeMalfunction timestampRepair timestamp
Forklift=(NETWORKDAYS.INTL(O4;P4;"0000000")-1)*(J4-I4)+MOD(P4;1)-MOD(O4;1)05:0001:00Malf. date + timeRepair date + time
 

Attachments

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

burnthepapper

New Member
Joined
Nov 9, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top