Formula Errors

How to Calculate Overlapping Hours Between Time Periods in Excel

John Wilson Oct 14, 2026 869 views

Question details

The user wants to find a formula to calculate the exact number of hours that overlap between two different date-and-time ranges.

How to Calculate Overlapping Hours Between Time Periods in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee shifts, scheduling tasks, or managing project timelines where calculating the exact overlap of two time intervals is required.
Observed behavior: Needs a formula that returns the overlap in hours, accounts for different start and end times, and correctly returns zero when there is no overlap at all.

Before you start

Ensure that your start and end times are formatted as proper date-time values in Excel so the formula can correctly evaluate the differences.

Solution 1Recommended

Use the MIN and MAX Formula to Find Overlap

This straightforward formula compares the start and end times of both periods, extracts the overlapping duration, and converts it into hours.

Excel handles dates and times as serial numbers, where one whole day equals 1. By subtracting the highest start time from the lowest end time, you can find the overlapping fraction of the day. Multiplying by 24 converts this fraction into hours.

Enter the first time period

Type the start date and time of the first period in cell A1 and the end date and time in cell B1.

Enter the second time period

Type the start date and time of the second period in cell A2 and the end date and time in cell B2.

Input the calculation formula

Select an empty cell where you want the result to appear and type the formula: =MAX(0,MIN(B1,B2)-MAX(A1,A2))*24.

Format the result

Press Enter. If the result displays as a time or date, right-click the cell, select 'Format Cells', and change the format to 'Number' or 'General'.

Use the MIN and MAX Formula to Find Overlap

Formula Breakdown: The MAX(0, ...) portion ensures that if the time periods do not overlap at all (which would normally produce a negative number), the formula safely returns zero.

Use WPS Spreadsheet

Calculate Time Overlaps Easily with WPS Spreadsheet

WPS Spreadsheet fully supports Excel's MIN and MAX functions, allowing you to calculate time and date differences without any compatibility issues or learning curves.

1. Open your schedule data: Launch WPS Spreadsheet and open the document containing your time tracking data.
2. Apply the overlap formula: Click the cell where you want the result, and enter =MAX(0,MIN(B1,B2)-MAX(A1,A2))*24.
3. Calculate the result: Press Enter to instantly calculate the overlapping hours.
4. Adjust cell formatting: Use the formatting toolbar at the top to set the cell to 'Number' format if it defaults to a date.

100% compatible with Microsoft Excel formulas and functionsFree, lightweight, and fast spreadsheet processingBuilt-in robust date and time formatting optionsFamiliar user interface for seamless workflow migration

microsoft office alternative - wps office

Frequently Asked Questions

Why is my overlap result displaying as a strange date instead of hours?

Excel often automatically applies a Date or Time format to cells containing date-time calculations. To see the overlapping hours, right-click the result cell, select 'Format Cells', and change the formatting to 'General' or 'Number'.

Can I calculate time overlap excluding weekends and holidays?

Yes, but it requires a more advanced setup. You will need to use the NETWORKDAYS function combined with time functions, or utilize Excel 365's Power Pivot and Power Query features to account for specific working schedules and rest periods.

What does multiplying by 24 do in this formula?

Excel stores time as a fraction of a 24-hour day (e.g., 12 hours is stored as 0.5). Multiplying the resulting day fraction by 24 converts it into standard decimal hours, making it easier to read and use for payroll calculations.

Excel & Data Problems