4 Ways To Calculate Days Between Dates in Microsoft Excel

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.

Sample dataset 1

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.

Using DAYS formula
Using DAYS formula

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

Setting up a function
Setting up a function

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.

Calculate days from dates using DAYS
Calculate days from dates using DAYS

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

Using the fill handle
Using the fill handle

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")
Computed days from dates using DATEDIF
Computed days from dates using DATEDIF

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:

Sample dataset 2

Organize your dataset as shown above.

Configuring NETWORKDAYS
Configuring NETWORKDAYS

In D2, enter the following formula:

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

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 without holidays
NETWORKDAYS without holidays
=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:

Sample dataset 3

Restructure your working dataset as shown above.

Setting up the formula
Setting up the formula

In F2, enter the following formula:

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

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:

Changing the weekend code
Changing the weekend code
=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.

Default weekend codes
Default weekend codes

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:

Default weekend codes formula
Default weekend codes formula
=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.

Sample dataset 4

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.

Short date formatting
Short date 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.

Apply Custom formatting
Apply Custom formatting

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.

Setting up the TODAY function
Setting up the TODAY function

Now, in C2, enter the following formula:

=TODAY()-B2
Calculate TODAY
Calculate TODAY

Hit Enter to calculate the number of days.

Use fill handle for TODAY
Use the fill handle for TODAY

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.

Get Data command
Get Data command

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

From SQL Database
From SQL Database

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.

From Table Range
From Table Range

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.

Transform with Date Only
Transform with Date Only

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.

New column name
New column name

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.

Enter Power Query formula
Enter Power Query formula

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

Duration.Days([End Date]-[Start Date])
Calculated days in Power Query
Calculated days in Power Query

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

Close and load to
Close and load to

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 rows
Delete rows

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.

VBA script 1
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
Macro dialog box
Macro dialog box

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

Click on the CalculateDaysBetweenDates macro and hit Run.

Start date dialog
Start date dialog

On the Start Date dialog, enter the initial date.

The deadline dialog
The deadline dialog

On the End Date dialog, enter the deadline.

Total days dialog box
Total days dialog box

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:

VBA script 2
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:

Choose start date list
Choose start date list
  • A dialog box to choose the start date range.
Choose end date list
Choose end date list
  • Another dialog box to select the end date range.
Excluding weekends pop up
Excluding weekends pop-up
  • A confirmation dialog to include or exclude weekends.
Choose destination cell range
Choose destination cell range
  • An input box so you can choose the destination of results.
Calculation of days between two dates in Excel using VBA
Calculation of days between two dates in Excel using VBA

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:

VBA script 3
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:

Custom field to choose start range
Custom field to choose start range
  • An input field so you can select the range of start dates.
Custom field to choose end range
Custom field to choose end range
  • Choose the range of end dates in the next input box.
Choice to exclude or include weekends
Choice to exclude or include weekends
  • A message box asking to include or exclude weekends.
Choose a list of custom holidays
Choose a list of custom holidays
  • An input field so you can choose the range for end dates.
Select destination cell range
Select destination cell range
  • Select the destination cell range on the next input box.
Calculated days from dates excluding holidays and weekends
Calculated days from dates excluding holidays and weekends

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃