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)