Calculate machine utilization

This article explains how to calculate an overlapping time ranges across multiple days. This can be very useful in situations like:

• Accurately calculating usage of equipment, vehicles, machines etc.
• Determining how much to bill a client when a service is provided.
• Calculating energy consumption based on usage time.
• Measuring employee hours worked.

1. Calculate total time based on a time range across multiple days

This example demonstrates a formula that calculates the total time based on a time range that repeats ever day. The start time is 8:15:00 PM and is displayed in cell C5. The end time is 12:27:13 PM which is shown in cell C6. This time range repeats everyday. Note that the time range contains hours, minutes, and seconds.

The question is what is the total time if the range is between

• 1/7/2028 9:15:16 AM
• 1/13/2028 7:42:49 AM.

The following formula calculates the total time based on these conditions:

There are three different calculations in this formula:

1. The overlapping time for the start date (1/7/2028) only.
   • MEDIAN(C5,C6,1)-MEDIAN(C5,C6,MOD(C10,1))
2. The overlapping time for the end date only (1/13/2028).
   • MEDIAN(C5,C6,MOD(C11,1))-MEDIAN(C5,C6,0)
3. Total time based on days between the start date and end date excluding the start and end date.
   • (INT(C11)-(INT(C10)+1))*(C6-C5)

1.1 First calculation

This calculation returns the total overlapping time for the first date. The MEDIAN function lets you calculate overlapping time between two different date and/or time ranges. The MOD function removes the integer part of a number if the second argument is 1. The overlapping time for the start date/time is between 9:15:16 AM and 12:27:13 PM which is 3:11:57 meaning 3 hours, 11 minutes and 57 seconds. Excel uses decimal numbers

• MEDIAN(C5,C6,1)-MEDIAN(C5,C6,MOD(C10,1))

C5 is the start of the time range. C6 is the end of the time range. C10 is the start of the date/time range. 1 represents 12:00 AM.

MEDIAN(C5,C6,1) returns 0.518900462962963 which represents 12:27:13 AM.
MEDIAN(C5,C6,MOD(C10,1)) returns 0.38560185184906 which represents 9:15:16 AM.
0.518900462962963 - 0.38560185184906 equals 0.133298611 which is 3:11:57
The first day 1/7/2028 has 3 hours 11 minutes and 57 seconds overlapping.

1.2 Second calculation

This calculation returns the total overlapping time for the last date. The overlapping time for the end date/time is between 7:42:49 AM and 12:27:13 PM which is 0 (zero). They don't overlap.

1.3 Third calculation

The last calculation adds the time for days between the start and end date/time excluding the start and end date/time.

• (INT(C11)-(INT(C10)+1))*(C6-C5)

Dates are integers in Excel. There are 5 days between 1/7/2028 and 1/13/2028 if we exclude the start and end dates, they are: 1/8/2028, 1/9/2028, 1/10/2028, 1/11/2028 and 1/12/2028.

The time range for each day is 12:27:13 PM - 8:15:00 AM equals 3 hours, 11 minutes, and 57 seconds. The calculation in Excel is 0.518900462962963-0.34375 equals 0.175150462962963 which is 3 hours, 11 minutes, and 57 seconds.

5*0.175150462962963 = 0.875752314814815 which is 21 hours, 1 minute, and 5 seconds.

1.4 Calculating the total

The times from each calculation above are:

• 3 hours 11 minutes and 57 seconds or 0.133298611.
• 0 (zero) from the second calculation.
• 21 hours, 1 minute, and 5 seconds or 0.875752314814815 from the third calculation.

The total becomes 24 hours, 13 minutes, and 2 seconds or 1.00905092592872.

These articles are related:

• How to sum overlapping time based on minutes
• How to calculate overlapping time ranges
• Units contained in a range that overlap another range

2. Calculate total time based on different weekday time ranges overlapping multiple days

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 AM - 3:15 PM
Tue: 06:15 AM - 3:15 PM
We: 06:15 AM - 3:15 PM
Thu: 06:15 AM - 3:15 PM
Fri: 06:15 AM - 11:15 AM
Sat:
Sun:

The night shift hours:

Mon: 8:00 PM - 12:00 AM
Tue: 8:00 PM - 12:00 AM
We: 8:00 PM - 12:00 AM
Thu: 9:00 PM - 12:00 AM
Fri: 12:00 AM - 2:45 AM
Sat:
Sun:

Machine utilization date and time range
Start date and time: 1/2/2027 08:00 AM
End date and time: 1/11/2027 12:00 PM

The image above shows the start date and time value in cell C4 and end date and time value in cell C5. This date and time range will overlap the day and night shift hours, some times overlap multiple times.

Cell B9 to E19 contains the day and night shift hours specified per weekday. This example shows a week starting with Monday to Sunday with corresponding weekday numbers in cells B9 to B19. These numbers are important, they allow for calculating time total based on an overlapping day and night shift schedule.

Formula in cell F21:

Date and time range 1/2/2027 8:00 AM - 1/11/2027 12:00 PM overlaps these day and night shift ranges:

The right-most column in the table above contains the total time per each day and night shift schedule in a week. The following formula creates this dynamic array shown in cell range F9:F19.

Formula in cell F9:

This formula in cell F9 is similar to the formula in cell F24, the difference is that the formula breaks down the hours per each day in the given date and time range specified in cells C4 and C5. The formula in cell F9 breaks down the total per schedule row given in cell range B9:F19.

The formula in cell F24 is the same as in cell F21 with one exception, the SUM function is removed. This creates a dynamic array that spills to cells below automatically as far as needed. If you change the date and time range in cells C4 and C5 the formula automatically adjusts the output. This is great for a breakdown of the hours for each day in the given date and time range.

This example shows a date and time range that overlaps Mondays twice which creates a larger total for that day than the specified time range. For example, 3:15 PM - 6:15 AM is 9:00 hours but the total is 14:45:00 for the first row in the day and night schedule. This is explained by the date and time range overlapping this time range twice making the total larger than the given time range.

If you add the time intervals in cell range F9:F19 you will get 79:45 hours, you will get the same result if you add the time intervals in cells F24:F33.

Explaining the formula

The MEDIAN function allows you to calculate the overlapping time range based on two date and time ranges.

MEDIAN(Start1,End1,End2) - MEDIAN(Start1,End1,Start2)

1. Start1 is the starting point for date and time range 1
2. End1 is the ending point for date and time range 1
3. Start2 is the starting point for date and time range 2
4. End2 is the ending point for date and time range 2

The MEDIAN function is not capable of calculating a result based on an array of intervals, that is until now. The new Excel 365 BYROW function changes this, it is now possible to calculate overlapping date ranges across multiple intervals.

By employing two distinct BYROW functions, we can achieve the following:

1. Iterate through all days within the specified date and time range.
2. Iterate through each row in the day and night schedule.

This allows us to calculate both the total and a detailed breakdown of the total across all days in the given date and time range. Furthermore, it also enables the calculation of a breakdown of the total per row in the day and night schedule.

The array that displays the detailed breakdown of the total across days indicates no values for both Saturday and Sunday. This is accurate because the schedule does not contain any intervals for those particular weekdays.

The total in cell F21 has the following formatting applied: [h]:mm:ss This allows hours being greater than 24 which is the number of hours in one day. This format is useful for displaying long durations larger than 24 hours.

The WEEKDAY function enables us to calculate numbers representing the weekday starting from 1 (Monday) to 7 (Sunday) for each day in the date and time interval. This makes the data coherent to the day and night schedule that is based on weekday numbers. The MEDIAN function can use these values to calculate a time value that represents overlapping interval.

Get Excel *.xlsx file

machine-calcv3.xlsx

Dates category

How to create date ranges in Excel
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 […]

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Excel categories

Comments (1)