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: I created a workbook for this calculation. Yellow cells are input cells. Red cell is the output of this calculation in hours and minutes.

Download excel template file:
machine-utilization
(Excel 97-2003 Workbook *.xls)

Here are some screenshots of this template. Everything in this template is calculated and displayed on Sheet1. Remember only yellow cells are input cells.

Related posts:

1. How to calculate missing months in a given date range in excel
2. Calculate occurences the past 90 days in excel