How To Count the number of days, weeks, months or years between two dates?

When working with date data in Excel, you may often need to determine the exact number of days, weeks, months, or years that separate two specified dates. This calculation is commonly required for project scheduling, employee tenure analysis, financial forecasting, and more. Excel provides several user-friendly methods to calculate these differences, including direct formulas, built-in features, and advanced tools that can streamline or automate the process. This tutorial provides step-by-step instructions on various approaches to accurately compute date differences, helping you select the most suitable solution for your scenario.

• Count the number of days between two dates with formulas
• Count the number of weeks between two dates with formulas
• Count the number of months between two dates with formulas
• Count the number of years between two dates with formula
• Count the number of years months and days between two dates with formulas
• Calculate various difference between two dates in days, weeks, months, years with a powerful feature
• VBA macro: Automate calculation of days, weeks, months, or years between two dates for batch processing

Count the number of days between two dates with formulas

Calculating the number of days between two dates is one of the most frequent date-related tasks in Excel. Whether you're tracking deadlines, calculating durations, or monitoring accrual periods, Excel formulas can help you achieve these calculations accurately.

1. Enter any one of the following formulas into a blank cell where you want the result to appear:

Note: Here, A2 should contain the start date, and B2 the end date.

2. Drag the fill handle down to apply the formula to other rows as needed. The cell will display the number of days between the corresponding start and end dates.

Tips and notes:

• If the end date is earlier than the start date:
  • The formula =B2 - A2 will return a negative number.
  • The formula =DATEDIF(A2, B2, "D") will return a #NUM! error.
    So be sure to verify your input dates for accuracy.
• The DATEDIF function ignores the time portion and calculates full days only.
• For inclusive day counts (including both start and end dates), add +1 at the end of your formula:
  e.g., =DATEDIF(A2, B2, "D") + 1 or =B2 - A2 + 1.

Count the number of weeks between two dates with formulas

Determining the number of weeks between two dates can be essential for payroll periods, recurring events, or academic schedules. Excel makes this calculation straightforward with the formulas below. This approach is best for basic week calculations; for calendar-based week counting, additional adjustment may be required.

1. Enter any one of the following formulas into a blank cell:

Note: In these formulas, A2 is the starting date, and B2 is the ending date.

2. Extend the formula by dragging the fill handle to apply it to the desired rows. The result will show the number of weeks as a decimal. This method is especially helpful for finding out exact week counts, but if you need only complete weeks (whole numbers), refer to the tip below.

Additional notes: If you need to align weeks with specific weekdays (e.g., always starting on Mondays), you might use auxiliary columns or the WEEKNUM function for tailored calculations.

Count the number of months between two dates with formula

Calculating the number of months between two dates is useful for tracing periods such as loan tenures, time to maturity, or project phases. The DATEDIF function is designed for such interval-based date calculations.

1. Input this formula in the cell where you want the result:

Note: Use A2 for the starting date and B2 for the ending date.

2. Drag the fill handle down as needed. This formula yields only the number of complete months in the given interval.

Notes: The DATEDIF function treats months as whole units, so partial months are not counted unless explicitly requested as in the combined formula above. Adjust input formats to ensure date fields are recognized as valid dates by Excel, otherwise errors may occur.

Count the number of years between two dates with formula

For annual reports, employment anniversaries, or other scenarios where full years elapsed are important, Excel lets you calculate this efficiently with the DATEDIF function.

1. Enter the formula below in a blank cell:

Note: Reference A2 as the starting date and B2 as the ending date.

2. Use the fill handle to apply the formula to all required cells. The calculation will return the number of complete years between the two dates. This is very useful for quickly determining service years or establishing eligibility thresholds for certain benefits.

Troubleshooting: If the dates are formatted as text and not as real Excel dates, the formula may return errors. Convert them with DATEVALUE if necessary.

Count the number of years months and days between two dates with formulas

In cases where you need a more detailed breakdown (such as for age calculations or detailed tenure reporting), you can combine DATEDIF functions to show years, months, and days between two dates.

1. Insert or paste the following formula in a cell:

Note: Use A2 for the starting date and B2 for the ending date.

2. Drag the formula as necessary. This approach calculates and displays the difference in a combined, text-based format (e.g., "2 years 3 months 7 days").

Tips: This result is especially helpful for documenting employee age, contract durations, or customer relationships where full breakdowns are required. If you need the result in separate cells, split the formula or use helper columns.

Calculate various difference between two dates in days, weeks, months, years with a powerful feature

If you're not comfortable working with formulas or if you would like the flexibility to quickly switch between different units (days, weeks, months, years, combinations), Kutools for Excel simplifies this process with the Date & Time Helper.

After completing installation of Kutools for Excel, follow the instructions below:

1. Click a cell for the result. Go to Kutools > Formula Helper > Date & Time helper as shown:

2. In the Date & Time Helper dialog:

• Select the Difference option under Type;
• Choose the start date and end date in the Arguments input boxes;
• Set the result type as desired (Year + Month + Week + Day, etc.) for a custom breakdown.

3. Click OK to calculate. Extend the result by dragging the fill handle if you need to cover more data rows.

Click to Download Kutools for Excel and free trial Now!

This solution is especially recommended if you regularly need to perform mixed or unusual date calculations, or want a no-formula solution that is flexible and efficient.

VBA Macro: Automate calculation of days, weeks, months, or years between two dates

For users handling large-scale data or automating repetitive calculations across multiple rows, using a VBA macro can boost efficiency. This approach is suitable when you need to process many date pairs at once, or when copying formulas is impractical. With VBA, you can calculate differences in days, weeks, months, or years across a defined range, and even customize the output format as needed. This is particularly effective for periodic reports or batch processing tasks.

1. Open the VBA editor by navigating to Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then, copy and paste the following code into the module:

Sub CalculateDateDifferences()
    Dim WorkRng As Range
    Dim RowRng As Range
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim OutputCol As Integer
    Dim DiffType As String
    Dim xTitleId As String
    
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    
    Set WorkRng = Application.InputBox("Select the range of date pairs (two columns: Start and End Date)", xTitleId, Selection.Address, Type:=8)
    
    If WorkRng Is Nothing Then Exit Sub
    
    StartCol = WorkRng.Columns(1).Column
    EndCol = WorkRng.Columns(2).Column
    OutputCol = EndCol + 1
    
    DiffType = Application.InputBox("Enter difference type: D=Days, W=Weeks, M=Months, Y=Years", xTitleId, "D", Type:=2)
    
    For Each RowRng In WorkRng.Rows
        If IsDate(RowRng.Cells(1, 1)) And IsDate(RowRng.Cells(1, 2)) Then
            Select Case UCase(DiffType)
                Case "D"
                    RowRng.Cells(1, 3).Value = RowRng.Cells(1, 2).Value - RowRng.Cells(1, 1).Value
                Case "W"
                    RowRng.Cells(1, 3).Value = (RowRng.Cells(1, 2).Value - RowRng.Cells(1, 1).Value) / 7
                Case "M"
                    RowRng.Cells(1, 3).Value = DateDiff("m", RowRng.Cells(1, 1).Value, RowRng.Cells(1, 2).Value)
                Case "Y"
                    RowRng.Cells(1, 3).Value = DateDiff("yyyy", RowRng.Cells(1, 1).Value, RowRng.Cells(1, 2).Value)
                Case Else
                    RowRng.Cells(1, 3).Value = "Invalid Type"
            End Select
        Else
            RowRng.Cells(1, 3).Value = "Invalid date(s)"
        End If
    Next
    
    Application.DisplayAlerts = True
    MsgBox "Date differences calculated in the third column of your selected range.", vbInformation, xTitleId
End Sub

2. Click the button to run the macro. You will be prompted to select your range with start and end dates (two adjacent columns). Then, enter the difference type: D for days, W for weeks, M for months, Y for years. The result will appear in the column immediately to the right of your selected range.

Tips for use: This macro is ideal for fast, consistent batch processing. Ensure your date columns are properly formatted as dates to avoid errors. If you encounter "Invalid date(s)" in the result column, review your range for non-date values or empty cells. This method is well-suited for advanced users or regular bulk-reporting scenarios.

More date and time related articles: