For scheduling tasks, it is often needed to calculate the hours between two times. Calculating the difference between two values of time is easy using the minus (-) operator. However, that does not work when the time goes past midnight. In this article, we will learn how to calculate hours between two times after midnight in excel.
➤ Take a helper column to calculate the hours.
➤ Write this formula:
=IF(B2>C2,C2+1,C2)-B2
➤ Replace B2 with the first time, and C2 with the second time.
➤ From the Home tab, go to Number > More Number Formats….
➤ Set the cell format to Time > 13:30
➤ Shorter alternative with the same result: =MOD(C2-B2,1)
➤ For decimal hours, such as 8.5 instead of 8:30: =MOD(C2-B2,1)*24, formatted as Number
That was a quick summary of the method. But I am sure you want to learn more about how the formula works. Keep reading for a detailed lesson on the functions so that you understand and don’t just have to memorize the method.
Steps to Calculate Hours Between Two Times After Midnight In Excel
In this practice dataset, we have some task schedules, with the task description and the start/end time. We want to extract the number of hours in between. The steps below should do it:
Step 1: Add a Helper Column
➤ We are taking the rightmost column and adding the header ‘Hours Between’ to it.
➤ Choosing a column with the same rows makes it easier to autofill and read the answers.
Step 2: Enter the Formula to Calculate Hours Across Midnight
➤ Under the heading of the newly created column, use this formula:
=IF(B2>C2,C2+1,C2)-B2
A shorter formula gives exactly the same result: =MOD(C2-B2,1). MOD adds the missing day automatically whenever the subtraction goes negative. Both versions have one limit – they assume the shift is shorter than 24 hours, so a shift from 08:00 to 08:00 the next day returns 0:00. For shifts that long, enter the date and the time in the same cell and subtract directly, then see how to add time in Excel over 24 hours for the format that displays it.
➤ Autofill the other cells with the same formula.
Step 3: Format the Result as Hours or Decimal Hours
➤ The formula returns a fraction of a day, so until you format it the result may show as a decimal such as 0.333 or as a clock time such as 8:00 AM. Set it to the Time format to read it as hours and minutes. If you want a plain number of hours instead, such as 8.5 rather than 8:30, use =MOD(C2-B2,1)*24 and format the cell as Number. And if you later add these results up for a whole week, the total can pass 24 hours – see how to calculate total hours in Excel for the format that stops it resetting.
➤ Select the cells of the new column, other than the heading.
➤ From the Number section, open the dropdown menu, and select More Number Formats…
➤ From the new dialog box, select Time from the left pane, 13:30 from the right pane, and press OK.
➤ The data in the new column should make sense now.
Downloadable Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Calculate Hours Between Two Times after Midnight.xlsx
Frequently Asked Questions
How to calculate hours between two times in Excel?
Use this formula, which works whether or not the shift crosses midnight:
=MOD(C2-B2,1)*24
Replace C2 with the end time and B2 with the start time. A shift from 22:00 to 06:00 returns 8, and 09:00 to 17:30 returns 8.5. If you only want whole hours, wrap it as =INT(MOD(C2-B2,1)*24). Plain subtraction such as =C2-B2 goes negative once the end time is past midnight, which is why it is not used here. For other time-difference cases, see how to calculate the difference between two times.
How do you check if a time is between two times in Excel?
Enter this formula:
=IF(MOD(D2-B2,1)<=MOD(C2-B2,1),"Yes","No")
Here, B2 is the start time, C2 is the end time and D2 is the time you are checking. It measures how far D2 sits after the start and compares that with the length of the whole window, so it works for a normal window such as 09:00-17:00 and for an overnight window such as 22:00-06:00, where a check time of 01:00 correctly returns Yes.
How do you generate a random time between two times in Excel?
This formula should suffice:
=MOD(B2+RAND()*MOD(C2-B2,1),1)
Replace B2 with the start time and C2 with the end time. The MOD parts keep the result inside the window even when it crosses midnight, so 22:00 to 06:00 only ever returns times in that overnight span. Format the cell as Time.
What is the formula for hours between two times decimal in Excel?
Here is the formula:
=MOD(C2-B2,1)*24
Here, B2 is the start time and C2 the end time. Format the cell as Number, not Time. A 22:00 to 06:00 shift returns 8, where the plain =(C2-B2)*24 would return -16.
How to calculate the difference between two dates?
You can just subtract the dates, like this:
=C2-B2
In this formula, C2 should be the end date, and B2 should be the start date. For months, years and working days, see how to calculate elapsed time between two dates.
Wrapping Up
In this article, we have learned how to calculate hours between two times after midnight in excel. The practice file contains the data set used in this article, including the formula. Feel free to comment with your suggestions on what topics you want us to cover. Consider bookmarking the site if you want to learn more excel tips and tricks.
