How to calculate hours between times after midnight in Excel?
Suppose you are managing a work schedule in Excel, and you need to track employee work periods that start late one day and finish after midnight, spanning into the next day. For example, Column A records start times on the current day, and Column B logs end times after midnight on the following day. When you attempt to calculate the time difference using a simple subtraction formula, such as "=B2-A2", Excel displays an incorrect or negative result, as shown in the screenshot below. This happens because Excel time calculations reset at midnight, causing direct subtraction to yield erroneous values for periods crossing midnight. How can you accurately calculate the total work hours between two times when the end time is after midnight in Excel? 
Calculate the hours between two times after midnight with a formula
Calculate hours between times after midnight with VBA code (Advanced Automation)
Calculate the hours between two times after midnight with a formula
To get an accurate calculation of hours between two times where the end time is after midnight, you can use a simple formula that accounts for time differences spanning two days. This approach is efficient for small to moderately sized datasets or when your logic simply needs to handle time gaps crossing midnight.
How It Works: The formula "=(B2-A2+(B2<A2))*24" checks if the end time (B2) is earlier than the start time (A2). If so, it adds 1 day (since Excel stores time as fractions of a 24-hour day), ensuring you always get a positive hour calculation.
1. Enter the following formula into a blank cell next to your time data (for example, in cell C2):
=(B2-A2+(B2<A2))*24Explanation: Here, A2 contains the start time, and B2 contains the end time (after midnight). Adjust the cell references as needed based on your data layout.
2. After entering the formula, press Enter. Then use the fill handle to drag the formula down to other rows where applicable. Excel will instantly calculate the time differences, accounting for midnight transitions.
Tips:
- Ensure all time cells are formatted as Time (Right-click > Format Cells > Time).
- This formula outputs hours as a decimal. For a result in hours and minutes, you can use
TEXTformatting, e.g.,=TEXT(B2-A2+(B2<A2),"h:mm"), but the original formula is best when you need decimal hour calculations for payroll or reporting. - If your data includes invalid time entries, check for typos or values that aren’t recognized as time by Excel.
Common errors:
- If you get a #VALUE! error, double-check that both columns have valid time values and not text.
- Negative or unexpected results usually mean the formula references are offset or a time wasn’t entered correctly.
Summary: This solution is practical for typical shift schedules or simple overnight time tracking when your data fits in a straightforward two-column setup and you don't require more complex business logic.
Calculate hours between times after midnight with VBA code (Advanced Automation)
If you need to process a large amount of time log data, or you want to automate the calculation of hours for complex scenarios (such as applying custom rounding, exclusions, or variable breaks), using a VBA macro can save time and reduce manual errors. This method is suited for advanced users or those dealing with evolving shift patterns, unusual cutoff points, or batch processing needs.
Applicable scenarios: Choose this solution if you:
- Handle hundreds or thousands of rows regularly
- Need logic beyond a basic midnight crossover (e.g., variable overtime rules, lunch break deductions)
- Prefer a solution that recalculates automatically with minimal manual input
Drawbacks: Users must be comfortable enabling macros, and workbook recipients may also need to allow VBA or adjust macro security settings.
1. Open the Excel workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) editor. In the VBA interface, click Insert > Module to create a new module, and paste the code below into the code window:
Sub CalculateHoursAcrossMidnight()
Dim rng As Range
Dim cell As Range
Dim startTime As Date
Dim endTime As Date
Dim resultCol As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range containing start and end times (two columns)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resultCol = rng.Columns(rng.Columns.Count).Column + 1
For Each cell In rng.Columns(1).Cells
startTime = cell.Value
endTime = cell.Offset(0, 1).Value
If IsDate(startTime) And IsDate(endTime) Then
If endTime < startTime Then
cell.Offset(0, 2).Value = (endTime - startTime + 1) * 24
Else
cell.Offset(0, 2).Value = (endTime - startTime) * 24
End If
Else
cell.Offset(0, 2).Value = "Invalid Time"
End If
Next cell
MsgBox "Calculation complete! Hours between times after midnight are now in the next column.", vbInformation, xTitleId
End Sub2. To execute the macro, close the VBA editor and return to Excel. Highlight the range in your worksheet that includes both the start and end time columns. Then, press the F5 key or click the Run button.
The macro will prompt you to select a range containing both the start and end times (side by side). The results, as the number of hours between times (adjusted for midnight crossing), will be output in the column directly to the right of your data range.
Tips and troubleshooting:
- Both start and end time columns must have valid date/time values; cells with unrecognized formats will be flagged as "Invalid Time".
- To re-use or adapt the macro for other sheets, change the selection or tweak the column offsets as needed.
- If the macro is not visible or executable, ensure macros are enabled by adjusting your Excel security settings (File > Options > Trust Center > Macro Settings).
- This approach can be adapted to handle custom business rules; you can insert additional VBA logic for rest breaks, time rounding, or overtime calculations as needed.
Summary: The automated VBA method is especially helpful for users managing large rosters, requiring recurrent calculations, or implementing organization-specific logic for time management. Once set up, it eliminates repetitive manual work and minimizes the risk of calculation mistakes for overnight shifts.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in