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.

## 0 Comments