Calculate machine utilization
Question:
I need to calculate how many hours a machine is utilized in a company with a night and day shift?
The day shift hours:
Mon: 06:15 - 15:15
Tue: 06:15 - 15:15
We: 06:15 - 15:15
Thu: 06:15 - 15:15
Fri: 06:15 - 11:15
Sat:
Sun:
The night shift hours:
Mon: 20:00 - 07:45
Tue: 20:00 - 07:45
We: 20:00 - 07:45
Thu: 21:00 - 02:45
Fri:
Sat:
Sun:
Machine utilization
Start date and time: 2007-01-02 08:00
End date and time: 2007-01-05 12:00
Answer:
Calculate weekday num and time
I have the start date in cell B4 and the end date in cell B5.
First, we have to calculate weekday numbers and times.
Formula in C4:
Copy cell C4 and paste to cell C5.
Formula in D4:
Copy cell D4 and paste to cell D5.
Setup shift hours
I converted day and night shift hours into a table in cell range A9:D18.
Calculate utilized machine hours
Array formula in cell D21:
Explaining array formula in cell D21
Step 1 - Find a shift time interval where the machine starts
=SUMPRODUCT(COUNTIFS($C$4, $B$9:$B$18, $D$4, ">="&$C$9:$C$18, $D$4, "<="&$D$9:$D$18)*($D$9:$D$18))-$D$4
COUNTIFS($C$4, $B$9:$B$18, $D$4, ">="&$C$9:$C$18, $D$4, "<="&$D$9:$D$18)
becomes
COUNTIFS(2, {1; 1; 2; 2; 3; 3; 4; 4; 4; 5}, 0,333333333335759, ">="&{0,260416666666667; 0,833333333333333; 0; 0,833333333333333; 0; 0,833333333333333; 0; 0,260416666666667; 0,875; 0; 0,260416666666667}, 0,333333333335759, "<="&${0,635416666666667; 1; 0,635416666666667; 1; 0,635416666666667; 1; 0,114583333333333; 0,635416666666667; 1; 0,114583333333333; 0,46875})
and returns this array:Â {0;0;1;0;0;0;0;0;0;0;0}
Step 2 - Multiply with shift time hours
SUMPRODUCT({0;0;1;0;0;0;0;0;0;0;0})*($D$9:$D$18))
becomes
SUMPRODUCT({0;0;1;0;0;0;0;0;0;0;0})*({0,635416666666667; 1; 0,635416666666667; 1; 0,635416666666667; 1; 0,114583333333333; 0,635416666666667; 1; 0,114583333333333; 0,46875})) returns 0,635416666666667
Step 3 - Subtract shift hour with machine start time
0,635416666666667 - 0,333333333335759 equals 0,302083333333333 (07:15)
Array formula in cell D22:
Explaining array formula in cell D22
Step 1 - Identify remaining shift hours the day machine starts
=SUMPRODUCT(COUNTIFS($C$4, $B$9:$B$18, $D$4, "<="&$C$9:$C$18, $D$4, "<="&$D$9:$D$18)*($D$9:$D$18-$C$9:$C$18))
COUNTIFS($C$4, $B$9:$B$18, $D$4, "<="&$C$9:$C$18, $D$4, "<="&$D$9:$D$18)
becomes
COUNTIFS(2, {1; 1; 2; 2; 3; 3; 4; 4; 4; 5}, 0,333333333335759, ">="&{0,260416666666667; 0,833333333333333; 0; 0,833333333333333; 0; 0,833333333333333; 0; 0,260416666666667; 0,875; 0; 0,260416666666667}, 0,333333333335759, "<="&${0,635416666666667; 1; 0,635416666666667; 1; 0,635416666666667; 1; 0,114583333333333; 0,635416666666667; 1; 0,114583333333333; 0,46875})
and returns this array:Â {0;0;1;0;0;0;0;0;0;0;0}
Step 2 - Multiply with shift time hours
=SUMPRODUCT(COUNTIFS($C$4, $B$9:$B$18, $D$4, "<="&$C$9:$C$18, $D$4, "<="&$D$9:$D$18)*($D$9:$D$18-$C$9:$C$18))
becomes
{0;0;1;0;0;0;0;0;0;0;0}*($D$9:$D$18-$C$9:$C$18)
becomes
{0;0;1;0;0;0;0;0;0;0;0}*{0,375; 0,166666666666667; 0,635416666666667; 0,166666666666667; 0,635416666666667; 0,166666666666667; 0,114583333333333; 0,375;0,125; 0,114583333333333; 0,208333333333333}
and becomes
SUMPRODUCT({0;0;0;0,166666666666667;0;0;0;0;0;0;0}) and returns 0,166666666666667 (04:00)
Array formula in cell D23:
=SUM(IF(WEEKDAY(INT($B$4)+ROW(OFFSET($A$1, 0, 0, INT($B$5)-INT($B$4)-1)), 2)=TRANSPOSE($B$9:$B$18), TRANSPOSE($D$9:$D$18-$C$9:$C$18))) + CTRL + SHIFT + ENTER
Explaining array formula in cell D23
To be continued...
Array formula in cell D24:
All above formulas in cell D27:
Get excel sample file for this tutorial.
machine-calc.xlsx
(Excel 2007 Workbook *.xlsx)
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
One Response to “Calculate machine utilization”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi
Seek your help in resolving my problem. i need to calculate the m/c engagement time using simple Excel sheet where i enter the m/c start time in Cell B3 and the end time C3 we need the m/c engaged time in D3.
We are Job order company.We operate in Two shifts. Depending upon the load we will plan the machines.
A shifts starts by 9.00 and Ends by 18.00 ; Tea break is from 10.30 to 10.45 and 15.00 to 15.15; Lunch is from 13.00 to 13.30. Similarly B shifts starts by 18.00 and Ends by 3.00 in next morning ; Tea break is from 19.00 to 19.15 and 00.15 to 00.30; Dinner is from 22.00 to 22.30. Please help me.
Regards
Sivakumar MP