Author: Oscar Cronquist Article last updated on October 13, 2017

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

### 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:

=WEEKDAY(B4, 2) + ENTER

Copy cell C4 and paste to cell C5.

Formula in D4:

=B4-ROUNDDOWN(B4, 0) + ENTER

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.

### Array formula in cell D21:

=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 + ENTER

### 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:

=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)) + ENTER

### 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:

=SUMPRODUCT(COUNTIFS(\$C\$5, \$B\$9:\$B\$18, \$D\$5, ">="&\$C\$9:\$C\$18, \$D\$5, ">="&\$D\$9:\$D\$18)*(\$D\$9:\$D\$18-\$C\$9:\$C\$18)) + ENTER

### All above formulas in cell D27:

=SUMPRODUCT(COUNTIFS(\$C\$4, \$B\$9:\$B\$19, \$D\$4, ">="&\$C\$9:\$C\$19, \$D\$4, "<="&\$D\$9:\$D\$19)*(\$D\$9:\$D\$19))-\$D\$4+SUMPRODUCT(COUNTIFS(\$C\$4, \$B\$9:\$B\$19, \$D\$4, "<="&\$C\$9:\$C\$19, \$D\$4, "<="&\$D\$9:\$D\$19)*(\$D\$9:\$D\$19-\$C\$9:\$C\$19))+SUM(IF(WEEKDAY(INT(\$B\$4)+ROW(OFFSET(\$A\$1, 0, 0, INT(\$B\$5)-INT(\$B\$4)-1)), 2)=TRANSPOSE(\$B\$9:\$B\$19), TRANSPOSE(\$D\$9:\$D\$19-\$C\$9:\$C\$19)))+SUMPRODUCT(COUNTIFS(\$C\$5, \$B\$9:\$B\$19, \$D\$5, ">="&\$C\$9:\$C\$19, \$D\$5, ">="&\$D\$9:\$D\$19)*(\$D\$9:\$D\$19-\$C\$9:\$C\$19)) + CTRL + SHIFT + ENTER