Excel Calculate Hours Between Two Times After Midnight (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Calculating time is usually seen for hourly wages-based workers. It may be seen that working is long, maybe work finishes after midnight. In that situation, calculating work time is a bit complicated. In this article, we are going to discuss how to calculate hours between times after midnight in Excel.


Excel Calculate Hours Between Two Times after Midnight: 3 Methods

We will show 3 methods to calculate hours between two times after midnight in Excel. We have a dataset to calculate the time difference. Where the start time is on the present day and the end time is on the next day. We will calculate the time difference between them.


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

We will apply a simple formula based on subtraction and comparison to get the time difference.

📌 Steps:

  • First, add a column on the right side of the dataset.

  • Now, go to Cell D5 and put the following formula.
=(C5-B5)+(C5<B5)

Simple Formula with Condition to Calculate Hours between Two Times after Midnight

  • Press the Enter button.

We get a result that is expressed as a fraction of a day.

  • Now, drag the Fill Handle icon downwards.

Simple Formula with Condition to Calculate Hours between Two Times after Midnight

  • Select all the resultant cells. Press the right button of the mouse.
  • Choose Format Cells from the Context Menu.

Simple Formula with Condition to Calculate Hours between Two Times after Midnight

  • We enter the Format Cells window.
  • Choose the Time option from the Number tab.
  • Now, select our desired form from the Type box.

Simple Formula with Condition to Calculate Hours between Two Times after Midnight

  • Finally, press OK to get the result.

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


2. Apply the Excel IF Function to Get the Difference Between Two Times After Midnight

In this method, we will use the IF function. This will calculate the time difference by comparing the start and end times in Excel.

The IF function checks whether a condition is met and returns one value if TRUE and another value if FALSE.

📌 Steps:

  • Copy and paste the following formula on Cell D5.
=IF(C5>B5,C5-B5,1-B5+C5)

IF Function calculate hours between two times in Excel

  • Press the Enter button to apply this formula.

Here, we get the result in the desired time format as cells are formatted already.

  • Now, drag the Fill Handle icon downwards.

IF Function calculate hours between two times in Excel

We get the same result as Method 1.

This formula will compare the start and the end times. If the end time is greater than the start time then subtract the start time from the end time. Otherwise, subtract the start time from 1 and then add the end time.

Related Content: Calculate Elapsed Time Between Two Dates in Excel


3. Apply Excel MOD Function

This MOD function can solve this most simple way. No need to think about other things when using the MOD function!

The MOD function returns the remainder after a number is divided by a divisor.

📌 Steps:

  • Put this formula on Cell D5.
=MOD(C5-B5,1)

Excel MOD Function to calculate hours

  • Press the Enter button and drag the Fill Handle icon downwards.

We get the result easily.


💬 Note

To get the result in the appropriate format, each time we need to change the format of the result cells.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we described how to calculate between two times after midnight. We used different functions to perform this. I hope this will satisfy your needs. Please give your suggestions in the comment box.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

2 Comments
  1. Thank you very much it is helpful

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo