How to calculate elapsed time/days/months/years between two datetimes in Excel?

When managing schedules, tracking project timelines, or analyzing event logs in Excel, you often need to determine how much time has passed between two specific points. For instance, given two lists—one with start times and another with end times—you might want to quickly calculate the elapsed time for each pair in the same row, as illustrated by the screenshot below. This can be extremely helpful for reporting working hours, age calculations, or monitoring deadlines.

Calculate elapsed time/days/months/years with formula

Use VBA code to calculate elapsed time directly

---

Calculate elapsed time/days/months/years with formula

Calculate elapsed time

Excel makes it straightforward to calculate the difference between two times—useful for scenarios such as employee shift tracking, calculating turnaround times, or monitoring project progress. For same-day times, follow these steps:

1. Click in a blank cell where you want the elapsed time to appear (for example, cell C2). Enter the following formula:

=IF(B2<A2,1+B2-A2, B2-A2)

A2 should contain the start time and B2 the end time. This formula handles cases where the end time might be earlier than the start time (for example, shifts spanning midnight), ensuring the calculation remains accurate.

2. After entering the formula, press Enter. To apply this formula to other rows, use the fill handle: Click the small square at the bottom-right corner of the cell, drag it downward to cover additional records. This enables you to calculate elapsed times for multiple entries.

3. Keeping the result cells selected, right-click to open the context menu and choose Format Cells. In the Format Cells dialog, under the Number tab, select Time from the left list. On the right, choose your preferred time format (such as hh:mm, h:mm:ss, etc.) to display the results as readable elapsed times.

4. Click OK to apply the formatting. The time values will now be displayed according to your selection, making it easier to interpret total durations.

Additional tips:

• Ensure both start and end time columns are formatted as valid Excel times to avoid errors.
• If you expect some end times to be on the next day (e.g., overnight shifts), the formula above accommodates that by adding 1 (representing a full day) when needed.
• If you encounter a "#VALUE!" error, check for blank or incorrectly formatted entries in your source cells.

Calculate elapsed days, months, or years

Finding the number of days, months, or years between two dates is common for project planning, service periods, or age calculations. Here’s an efficient approach for each type:

For elapsed days, click a blank cell (such as C2) and enter the formula:

=B2-A2

Here, A2 is the start date, and B2 is the end date. After pressing Enter, drag the fill handle down to calculate for additional rows.

Notes: Ensure both dates use Excel’s date format; otherwise, the result might be incorrect. If you prefer only to count whole days (ignoring hours/minutes), this formula is suitable.

To calculate months between two dates, enter in a blank cell:

=DATEDIF(A2,B2,"m")

This formula returns the number of complete months elapsed. If partial months matter, you can also display fractional months by combining with days.

For years (including partial years), use:

=DATEDIF(A2,B2,"y")

If you want the years as a decimal, you might try =DATEDIF(A2,B2,"m")/12 and format the result cell as a number for greater precision.

Precautions:

• If the end date precedes the start date, these formulas will return negative values—consider adding validation.
• Make sure DATEDIF is entered carefully; a misspelling will cause Excel to return "#NAME?" errors, as this function is not listed in Excel’s standard function menu.

Calculate elapsed years, months, and days in a combined format

If your task requires a more detailed breakdown (for example, "2 Years, 6 Months, 19 Days"), Excel can do this using the DATEDIF function in combination. This solution is ideal for employee service duration, age calculations, or any scenario where a clear separation of years, months, and days is needed.

Select a blank cell (e.g., C2), and input the following formula:

=DATEDIF(A2,B2,"Y") & " Years, " & DATEDIF(A2,B2,"YM") & " Months, " & DATEDIF(A2,B2,"MD") & " Days"

Then press Enter. This combines multiple DATEDIF calculations to produce a readable string showing the precise elapsed time span.

If you need to apply the formula to multiple rows, use the fill handle as before. It’s best to format the resulting column as "General" to ensure the text displays correctly.

Useful tip: If your calculation spans leap years or dates with varying month lengths, rest assured that DATEDIF calculates accurately according to calendar months and years.

Troubleshooting and reminders:

• Always double-check your source data for blank cells or incorrect date/time formatting.
• If you notice errors, try reformatting the input columns as "Date" or "Time" to standardize the entries.
• If formulas fail to update after editing the source cell, press F9 to force recalculation, or ensure automatic calculation is enabled in Excel’s options.

---

VBA code to calculate elapsed time between two datetimes

If you need to automate calculations or process large datasets, VBA is a useful tool. For example, you can merge elapsed time results into a single cell or customize the calculation as needed.

1. Go to Developer Tools > Visual Basic; a new Microsoft Visual Basic for Applications window will appear. Click Insert > Module and paste the following code:

Sub CalcElapsedTimeBySelection()
    Dim startRange As Range
    Dim endRange As Range
    Dim outputCell As Range
    Dim ws As Worksheet
    Dim i As Long
    Dim rowCount As Long
    Dim elapsedTime As Double
    Dim startTime As Variant, endTime As Variant
    Dim xTitleId As String
    
    xTitleId = "Kutools for Excel"
    On Error Resume Next
    
    ' Prompt user for ranges
    Set startRange = Application.InputBox("Select the range for Start Time:", xTitleId, Type:=8)
    If startRange Is Nothing Then Exit Sub
    
    Set endRange = Application.InputBox("Select the range for End Time:", xTitleId, Type:=8)
    If endRange Is Nothing Then Exit Sub
    
    Set outputCell = Application.InputBox("Select the top-left cell for output results:", xTitleId, Type:=8)
    If outputCell Is Nothing Then Exit Sub
    
    On Error GoTo 0
    
    ' Check matching range sizes
    If startRange.Rows.Count <> endRange.Rows.Count Then
        MsgBox "The start and end time ranges must have the same number of rows.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    ' Loop through rows
    rowCount = startRange.Rows.Count
    For i = 1 To rowCount
        startTime = startRange.Cells(i, 1).Value
        endTime = endRange.Cells(i, 1).Value
        
        If IsNumeric(startTime) And IsNumeric(endTime) Then
            elapsedTime = CDbl(endTime) - CDbl(startTime)
            ' Handle next-day (cross-midnight) case
            If elapsedTime < 0 Then elapsedTime = elapsedTime + 1
            outputCell.Offset(i - 1, 0).Value = elapsedTime
            outputCell.Offset(i - 1, 0).NumberFormat = "[h]:mm:ss"
        Else
            outputCell.Offset(i - 1, 0).Value = "Invalid Time"
        End If
    Next i
    
    MsgBox "Elapsed times calculated successfully for " & rowCount & " rows.", vbInformation, xTitleId
End Sub

2. Click the button to run the code. You'll be prompted to select the start time range, end time range, and where to output the elapsed time. The result will display in a time format.

This VBA approach is especially effective when you want to customize the calculation or format further and saves time if you are working with very large spreadsheets.

---