In this effortless Excel tutorial, I’ll show you how to calculate days between dates in Excel.

Do you often struggle to figure out the total days or working days between two dates? When calculating employee payroll, do holidays and weekends make it hard?

Before I learned about Excel and its handy functions for calculating days and dates, I had a lot of trouble figuring out how many days were in long date ranges. I used to go through calendar pages and count the days by hand.

I know how hard it can be to work with dates at home, school, and work. Today, I’ll show you, step-by-step with screenshots, how to calculate the number of days between calendar dates in different ways. I hope you’ll find some examples that match what you deal with every day.

Using the Date & Time Functions Wizard

If you’re new to Excel and its functions, this is the best method for the calculation of days between two dates in Excel.

Suppose, you want to find the total days between the start and end dates of the projects in the above dataset.

Here, columns B and C contain the start and end dates of projects. You want the corresponding days in column D.

Select the first cell below the column header Total Days, D2, and press Alt + M + E to bring up the Date & Time functions menu.

Click on DAYS in the context menu to bring up the Function Arguments dialog box to construct the formula for the DAYS function.

The cursor will automatically enter the End date field. You simply need to click on the cell that contains the project deadline.

Now, click on the Start date field and select the cell containing the project initiation date.

Click OK to get the result in the selected cell, D2.

Now, use the fill handle in the cell D2 and drag it down until the cell where data exists is parallel to columns B and C.

Excel will copy the DAYS function from D2 until D7.

Using Various Excel Functions

Besides the DAYS function, there are other handy Excel functions to calculate days from dates with different conditions. Let’s look at those below:

DATEDIF Function

Besides days, this function also calculates the number of complete months and years between two date ranges.

In this function, you need to enter a third argument after the Start and End dates. This is the unit in which you want the output. Let’s look at these arguments below:

• Y: Calculate complete years
• M: Find complete months
• D: Compute days

Let’s say there are start and end dates in columns B and C. You want to use the DATEDIF function in D2 to find the total days.

Type in the following formula into the destination cell, D2, and hit Enter:

=DATEDIF(B2,C2,"D")

Excel will quickly find the total days between the selected period.

NETWORKDAYS Function

This Excel function allows you to calculate days between two date ranges by subtracting weekends and a list of holidays. You can use this function in payroll to calculate wages for employees or contractors.

Let’s see below how to set up a formula using the NETWORKDAYS function:

Organize your dataset as shown above.

In D2, enter the following formula:

=NETWORKDAYS(B2,C2,$E$2:$E$6)

Hit Enter to get the days between two date ranges excluding weekends and holidays.

You can use the same formula to calculate days for multiple start and end dates. I’ve kept the list of holidays fixed in the formula so you can apply the same set of holidays to other date ranges.

If you don’t want to subtract holidays from the total days, use the following formula instead:

=NETWORKDAYS(B2,C2)

Here are the drawbacks of this function:

• Holidays must be entered in the Short and Long Date formats.
• For a date range spanning multiple calendar years, you must list holidays for all those years separately.
• The function is available from Excel 2013. If you’re using Excel 2010 and older, you won’t find this function.

NETWORKDAYS.INTL Function

Suppose, your organization follows a 24 by 7 work culture. Here, you’ll need to deal with multiple weekend combinations because you must allow 2 days weekly off to all the employees who are maintaining Monday to Friday and Saturday to Wednesday workdays.

So, you can’t use the NETWORKDAYS function to calculate workdays between two date ranges as this function only considers Saturdays and Sundays as weekends.

Here NETWORKDAYS.INTL comes to your rescue. You can choose from 17 different combinations of weekends with two and one weekly offs.

Also, you can use custom weekend combinations. For example, the weekend code 1100000 indicates Monday and Tuesday are the weekly off. The weekend code is 7 characters long and always starts with the Monday. So, for Mon and Tue to be excluded, the text string will be 1100000.

Let me demonstrate below the technique to calculate the number of workdays days between two dates in Excel using this function:

Restructure your working dataset as shown above.

In F2, enter the following formula:

=NETWORKDAYS.INTL(D2,E2,"1100000",$H$2:$H$6)

Hit Enter to get the total workdays excluding custom weekends and holidays.

For the next employee, Julie, you can’t use the same formula as her weekends are different than Jason’s.

You must customize the weekend code string in the above formula, which is "1100000".

For Juile, the weekend code is 0011000, which translates to Wednesday and Thursday week offs.

So the new formula will be as given below:

=NETWORKDAYS.INTL(D3,E3,"0011000",$H$2:$H$6)

If you don’t want to use the weekend code strings, you can follow a default weekend combination numbering shown by Excel. This is from 1 to 17.

As you select the weekend argument part in the formula, Excel will open a context menu of weekend codes as shown above.

For example, in the previous dataset, Thomas’s week off is Saturday. So, the formula to calculate his total workdays is as follows:

=NETWORKDAYS.INTL(D4,E4,17,$H$2:$H$6)

The drawback of this function is you must manually enter the weekend code if all the employees in your list don’t have the same weekend combination.

TODAY Function

Let’s assume you want to calculate the elapsed days since the start of a few projects until today. In this case, you can use the TODAY function and the subtraction sign calculation of days between two dates in Excel.

Start by organizing your worksheet dataset as shown above.

Before entering a formula in C2, you must optimize the Start Date and Elapsed Days columns with special number formatting.

Select the B2:B7 cell range and click on the General drop-down menu in the Number commands block.

Choose Short Date from the context menu.

Select the C2:C7 cell range and press Ctrl + 1.

On the Format Cells dialog, click on the Custom category and enter 0 in the Type field.

Click OK to save the custom cell formatting code.

Now, in C2, enter the following formula:

=TODAY()-B2

Hit Enter to calculate the number of days.

Now, use the fill handle in the cell C2 and drag it down to generate the total days elapsed till today for the rest of the projects.

Using Power Query

You must follow this method if your input dataset is so large that an Excel worksheet can’t accommodate that. That’s because Excel can only handle a limited number of rows and columns.

Power Query allows you to import data from most third-party databases. Also, you can export an existing Excel worksheet dataset to Power Query for data transformation.

To import a dataset from a different source, click on the Data tab and hit the Get Data drop-down menu.

Then, hover your mouse cursor over the preferred source, like From Database, From Azure, etc.

Excel will show an overflow menu from which you need to choose the final data source. For example, options you get here are From SQL Server Database, From Azure SQL Database, From Power BI (MSFT), and more.

To export a worksheet data to Power Query, select the dataset and press the From Table/Range command in the Get & Transform Data block of the Data tab.

Click OK on the Create Table dialog to complete the process.

On the Power Query, select the columns that contain the start and end dates.

Now, go to the Transform tab and click on the Date drop-down in the Date & Time Column.

From the context menu, choose Date Only.

Go to the Add Column tab and click on the Custom Column command.

On the Custom Column wizard, enter Total Days in the New column name field.

Into the Custom column formula field, enter the following Power Query formula:

Duration.Days([End Date]-[Start Date])

Click OK to apply the formula and create a new column.

Go to the File tab and click on the Close & Load To option.

You’ll see the Import Data dialog. There, click on the Existing worksheet option and select a destination cell range on the worksheet.

Delete or hide the old dataset.

Congratulations! You’ve successfully calculated days from date ranges in Power Query.

Using Excel VBA

Excel VBA lets you automate the whole process of calculating days between dates in Excel from selecting datasets, formatting output, applying calculation conditions, and populating results in a specific cell range.

If you’re new to Excel VBA, let me assure you it’s quite easy to implement such automation by using the VBA scripts mentioned in this article.

You only need to learn the technique to create a VBA macro from the following Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

If you’re ready, try using the following VBA scripts to create and run macros:

Calculate Days Using Visual Prompts

Use the following script to create a VBA macro that’ll walk you through the calculation process visually.

Sub CalculateDaysBetweenDates()
    ' Declare variables
    Dim startDate As Date
    Dim endDate As Date
    Dim totalDays As Integer

    ' Show input box to enter the start date
    startDate = InputBox("Enter the start date (mm/dd/yyyy):", "Start Date")

    ' Show input box to enter the end date
    endDate = InputBox("Enter the end date (mm/dd/yyyy):", "End Date")

    ' Calculate the total days count
    totalDays = endDate - startDate

    ' Show a message box showing the total days count
    MsgBox "The total days count is: " & totalDays, vbInformation, "Total Days"
End Sub

Once done creating the macro, press Alt + F8 to bring up the Macro dialog.

Click on the CalculateDaysBetweenDates macro and hit Run.

On the Start Date dialog, enter the initial date.

On the End Date dialog, enter the deadline.

Excel will show a notification dialog with the total days count.

⚠️ Warning: Create a copy of your workbook before running the VBA macros explained in this tutorial. You won’t be able to use the Excel undo feature to revert to the previous state of your worksheets.

Compute Days From a List of Start and End Dates

Use the following script to calculate total days from a custom list of start and end dates excluding the weekends:

Sub CalculateDays()

    ' Prompt the user to select the cell range for the start dates
    Dim StartRange As Range
    Set StartRange = Application.InputBox("Select the cell range for the start dates", Type:=8)

    ' Prompt the user to select the cell range for the end dates
    Dim EndRange As Range
    Set EndRange = Application.InputBox("Select the cell range for the end dates", Type:=8)

    ' Check if the ranges have the same size
    If StartRange.Count <> EndRange.Count Then
        MsgBox "The selected ranges must have the same size", vbCritical
        Exit Sub
    End If

    ' Ask the user if they want to exclude weekends
    Dim ExcludeWeekends As Boolean
    ExcludeWeekends = (MsgBox("Do you want to exclude weekends?", vbYesNo) = vbYes)

    ' Prompt the user to select the destination cell range
    Dim DestRange As Range
    Set DestRange = Application.InputBox("Select the destination cell range", Type:=8)

    ' Check if the destination range has the same size as the start and end ranges
    If DestRange.Count <> StartRange.Count Then
        MsgBox "The destination range must have the same size as the start and end ranges", vbCritical
        Exit Sub
    End If

    ' Calculate the number of days between the start and end dates
    Dim i As Long
    For i = 1 To StartRange.Count
        If ExcludeWeekends Then
            ' Exclude weekends
            DestRange.Cells(i).Value = Application.NetworkDays(StartRange.Cells(i).Value, EndRange.Cells(i).Value)
        Else
            ' Include weekends
            DestRange.Cells(i).Value = EndRange.Cells(i).Value - StartRange.Cells(i).Value
        End If
    Next i

End Sub

The macro will show the following input boxes to guide you visually:

• A dialog box to choose the start date range.

• Another dialog box to select the end date range.

• A confirmation dialog to include or exclude weekends.

• An input box so you can choose the destination of results.

Find above an example of the output of this macro.

Compute Days Excluding Custom Holidays

If you need to exclude holidays and weekends from project start and end dates, use the following VBA script:

Sub CalculateDays()

    ' Get the range for start dates
    Set startDateRange = Application.InputBox("Select the range for start dates", Type:=8)

    ' Get the range for end dates
    Set endDateRange = Application.InputBox("Select the range for end dates", Type:=8)

    ' Ask whether to exclude weekends
    ExcludeWeekends = MsgBox("Do you want to exclude weekends?", vbYesNo) = vbYes

    ' If excluding weekends, ask for the range for holidays
    If ExcludeWeekends Then
        Set holidayRange = Application.InputBox("Select the range for holidays", Type:=8)
    End If

    ' Get the range for output
    Set outputRange = Application.InputBox("Select the range for output", Type:=8)

    ' Calculate the number of days between each pair of start and end dates
    For i = 1 To startDateRange.Cells.Count
        startDate = startDateRange.Cells(i).Value
        endDate = endDateRange.Cells(i).Value

        ' Initialize the number of days to the difference between the end and start dates
        numDays = endDate - startDate + 1

        ' Subtract the number of weekend days and holidays if specified
        If ExcludeWeekends Then
            For d = startDate To endDate
                If Weekday(d, vbMonday) >= 6 Or Not IsError(Application.Match(d, holidayRange, 0)) Then
                    numDays = numDays - 1
                End If
            Next d
        End If

        ' Write the number of days to the output range
        outputRange.Cells(i).Value = numDays
    Next i

End Sub

Upon macro execution, Excel will show the following visual input boxes. Enter data accordingly:

• An input field so you can select the range of start dates.

• Choose the range of end dates in the next input box.

• A message box asking to include or exclude weekends.

• An input field so you can choose the range for end dates.

• Select the destination cell range on the next input box.

The above screenshot shows the performance and accuracy of this VBA script.

Conclusions

So, now you know how to calculate days between dates in Excel using a function wizard, popular Excel functions, Power Query, and Excel VBA.

If the article helped you in any way, give a shout-out in the comment box. If you’ve got any suggestions or tips, share those in your comments.