Calculate Hours Between Two Times After Midnight – 3 Methods

In the dataset below, the start time is on the current day and the end time is on the next day.

Method 1 – Use a Simple Excel Formula to Calculate Hours Between Two Times After Midnight

Steps:

• Add a column on the right side of the dataset.

• Go to D5 and use the following formula.

• Press Enter.

The result is returned as a fraction of a day.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Select the whole column. Right-click.
• Choose Format Cells in the Context Menu.

• In the Format Cells window, choose Time in Number.
• Select a time format in Type.

• Click OK to get the result.

Read More: How to Calculate Hours Between Two Times in Excel

Method 2 – Applying the Excel IF Function to Get the Difference Between Two Times After Midnight

Steps:

• Enter the following formula in D5.

• Press Enter.

The result is displayed in the selected time format.

• Drag down the Fill Handle to see the result in the rest of the cells.

This formula compares the start and the end times. If the end time is greater than the start time, it subtracts the start time from the end time. Otherwise, it subtracts the start time from 1 and adds the end time.

Method 3 – Applying the Excel MOD Function

Steps:

• Enter this formula in D5.

• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Calculate Hours and Minutes in Excel
• How to Calculate Hours from Date and Time in Excel
• How to Calculate Time Difference Between AM and PM in Excel
• How to Calculate Difference Between Two Dates and Times in Excel
• Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
• How to Calculate Travel Time Between Two Cities in Excel

<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel