Calculate Days Between Two Dates in Excel (Workdays/Weekends)

Calculating the number of days between two dates in Excel is a popular operation performed in different scenarios.

For example, project managers use it to measure performance against milestones and accountants use it to track aging receivables.

This tutorial shows you how to calculate the number of days between two dates in Excel in different situations.

Table of Contents

How to Calculate the Overall Number of Days Between Two Dates

In this first scenario, I will show you how to calculate the total number of days between two given dates. These would include all days, i.e., working days, weekends, and holidays.

Method #1: Use the Subtraction Method

Excel stores dates as sequential serial numbers.

This allows us to subtract the start date from the end date to get the total number of days between the two dates.

Below I have a data set where I have the start date and the end date, and I’ll create the total number of days between these two dates

We use the following steps:

Select cell D2 and type in the following formula:

=C2-B2

Subtraction formula to calculate days between two dates

Press Enter on the keyboard or click the Enter button on the Formula Bar.

Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Also read: How to Get Total Days in Month in Excel?

Method #2: Use the DATEDIF Function

The DATEDIF function is a legacy function maintained in Excel for the purpose of compatibility with Lotus 1-2-3.

The function calculates the number of years, days, or months between two dates. When you start to type it in a cell it does not appear as part of IntelliSense and must be entered manually.

Using the dataset in the previous illustration, we proceed as follows:

Select cell D2 and type in the following formula:

=DATEDIF(B2,C2,"d")

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click or drag down the fill handle to copy the formula down the column.

Let me quickly explain how this works.

Below is the syntax of the DATEDIF function

DATEDIF(start_date,end_date,unit)

The DATEDIF function has the following arguments:

The start_date is a required argument and stands for the starting date of a given period. In our example, it represents the start date of the project.
The end_date is a required argument and stands for the last date of a given period. In our example, it stands for the last date of the project.
The unit argument is a required argument, and it stands for the type of data you want the function to return. In our example, we want the function to return the number of days and therefore we use the “d” unit.

Note: You can also use the DATEDIF function to calculate the total number of months or number of years between two given dates. to do this, instead of using “d”, you can use “m” or “y”

Also read: Calculate Days Left in the Year (Formula)

Method #3: Use the DAYS Function

The DAYS function returns the overall number of days between two date values.

Using the dataset in the previous illustration, we proceed as follows:

Select cell D2 and type in the following formula:

=DAYS(C2,B2)

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Below is the syntax of the DAYS function

DAYS(end_date,end_date)

The DAYS function has the following arguments:

The start_date. It is a required argument and stands for the starting date of a given period. In our illustration, it represents the start date of the project.
The end_date. It is a required argument and stands for the last date of a given period. In our illustration, it stands for the last date of the project.

Also read: Convert Days to Months in Excel

How to Compute the Overall Number of Workdays Between Two Dates

Operations are normally shut down during weekends. You would want to exclude weekends when figuring out the number of workdays between two dates.

Below is the data set that I’m going to use for the illustration. It has the start date and the end date, and I want to calculate the total number of working days between these two dates (excluding the weekend dates).

Method #1: Use the NETWORKDAYS Function to Exclude Saturdays and Sundays

The NETWORKDAYS function returns the number of days between two dates excluding Saturdays and Sundays. It can also optionally exclude specified holidays but we shall illustrate that in the next method.

Below are steps to get the total number of days between two dates while excluding Saturday and Sunday:

Select cell D2 and type in the following formula:

=NETWORKDAYS(B2,C2)

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Below is the syntax of the NETWORKDAYS function:

NETWORKDAYS(start_date,end_date,[holidays])

The NETWORDAYS function has the following arguments:

The start_date. It is a required argument and stands for the starting date of a given period. In our illustration, it represents the start date of the project.
The end_date. This argument is a required argument and stands for the last date of a given period. In our illustration, it stands for the last date of the project.
The [holidays]. This argument is an optional argument. It represents a range of one or more dates such as federal and state holidays that are excluded from the working calendar. The dates to be excluded can be entered as a named range, a range of cells containing the dates, or a list of serial numbers representing the dates.

Method #2: Use the NETWORKDAYS Function to Exclude Saturdays, Sundays, and Holidays

The NETWORDAYS function by default excludes Saturdays and Sundays in its calculation.

If you want it to also exclude holidays, you have to pass to the function the dates to exclude.

The dates to be excluded can be entered as a named range, a range of cells containing the dates, or a list of serial numbers representing the dates.

Below I have a data set where I have the start date and the end date, and I also have a list of holidays. I want to calculate the total number of days between the start and the end date while excluding the holidays and weekend days.

Start and End Date dataset with holidays

Below are the steps to do this:

Select cell D2 and type in the formula below:

=NETWORKDAYS(B2,C2,$F$2:$F$5)

Note: Press F4 after typing in range F2:F5 to lock it down and make it an absolute reference.

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Days between two dates excluding holidays

While the NETWORKDAYS function is already built to take the start date and the end date and give you the total number of days between these two dates while excluding Saturdays and Sundays.

In this above example, we have also used the third optional argument that refers to the dates that are holidays.

So the final result that we get would give us the total number of days between the start date and the end date while excluding the weekend days and the holidays.

In case a holiday occurs on a weekend day, the function is smart enough to only recognize this and not exclude it twice.

Method #3: Use the NETWORKDAYS.INTL Function to Exclude Custom Weekends

One limitation of using the NETWORKDAYS function is that it defaults to excluding Saturday and Sunday.

In some countries or regions, weekends are Fridays and Saturdays or another combination of days.

In other situations, the weekend may be only Sunday or some other day. The NETWORKDAYS.INTL function takes care of such scenarios.

The NETWORKDAYS.INTL function returns the number of days between two dates excluding custom weekends. It can also optionally exclude holidays.

The syntax of the NETWORKDAYS.INTL function:

NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

The function has the following arguments:

start_date. It is a required argument. It represents a date value that is the starting date of a given period.
end_date. It is a required argument. It represents a date value that is the ending date of a given period.
[weekend]. It is an optional argument. It specifies the days of the week that are weekend days. The days can be specified by a weekend number or a string that shows when weekends occur. If this argument is omitted, the weekend days of Saturday and Sunday are assumed.

The weekend numbers range from 1 to 17. Each number represents a weekend day or weekend day.

[holidays]. It is an optional argument. It represents a range of one or more dates such as federal and state holidays that are excluded from the working calendar. The dates to be excluded can be entered as a named range, a range of cells containing the dates, or a list of serial numbers representing the dates.

We give an example of calculating the number of working days between two dates excluding Friday and Saturday as weekend days.

We use the following dataset:

We use the steps below:

Select cell D2 and type in the formula below:

=NETWORKDAYS.INTL(B2,C2,7)

Note: The third argument is weekend number 7 which represents the weekend days of Friday and Saturday.

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Days between two dates result excluding Fridays and Saturdays

The above formula gives us the total number of days between the start and the end date, while excluding all the Fridays and Saturdays.

Method #4: Use the NETWORKDAYS.INTL to Exclude Custom Weekends and Holidays

The NETWORKDAYS.INTL function returns the number of days between two dates excluding custom weekends. It can also optionally exclude holidays.

We give an example of calculating the number of working days between two dates excluding the weekend days of Wednesday and Thursday and holidays in a given range.

We use the following dataset:

We use the following steps:

Select cell D2 and type in the following formula:

=NETWORKDAYS.INTL(B2,C2,5,$F$2:$F$5)

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

The above formula gives us the total number of days between the start and the end date, while excluding the days that occur on Wednesdays and Thursdays and holidays

How to Calculate the Number of Custom Workdays Between Two Dates

We can use the NETWORKDAYS.INTL function to calculate the number of custom workdays between two dates.

Custom workdays could be the number of days one works at a part-time job.

Method #1: Use the NETWORKDAYS.INTL Function to Calculate the Number of Part-time Workdays

We give an example of calculating the number of custom workdays between two dates where an employee works part-time at a job on Mondays and Fridays only.

The dataset below is used in our example:

Select cell D2 and type in the following formula:

=NETWORKDAYS.INTL(B2,C2,"0111011")

Note: Instead of entering a weekend number in the third argument, we enter a string of seven characters. Each character stands for a day of the week beginning with Monday. Character 1 stands for a non-workday and 0 represents a workday. Therefore “0111011” represents the workdays of Monday and Friday.

NETWORKDAYS.INTL formula with custom days argument

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Note: If we have holidays we want to be excluded from the number of part-time workdays, we can pass the days to the function as the fourth argument.

Method #2: Use NETWORKDAYS.INTL Function to Calculate the Number of Fridays (or any other day) Between Two Dates

We can use the NETWORKDAYS.INTL function to calculate the number of specified days of the week between two dates.

Suppose we want to find out the number of Fridays between two dates. We use the following dataset to explain how this can be achieved.

We use the steps below:

Select cell D2 and type in the following formula:

=NETWORKDAYS.INTL(B2,C2,"1111011")

Formula to get number of Fridays between two dates

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

Note: The logic behind the argument of the seven-character string can be applied to calculate the total number of any other day between two dates.

How to Compute the Overall Number of Weekend Days Between Two Dates

Sometimes we may want to find out the number of weekend days between two dates.

We can use the combination of the DAYS function and the NETWORKDAYS function to calculate the number of weekend days between two dates.

The syntax of these two functions and their arguments have been explained previously in this tutorial.

The dataset below is used in our illustration:

We proceed as follows:

Select cell D2 and type in the following formula:

=DAYS(C2,B2)+1-NETWORKDAYS(B2,C2)

Note: We add 1 to the days returned by the DAYS function so as to include both the start date and the end date.

The logic of the formula is that the total number of workdays between two dates is subtracted from the total number of days between the two dates (the start date and end date inclusive).

The result is the number of weekend days between the two dates.

Formula to get number of weekend days between two dates

Press Enter key on the keyboard or click the Enter button on the Formula Bar.
Double-click the fill handle or drag down the fill handle to copy the formula down the column.

In this tutorial, I showed you different scenarios where you can use different formulas to calculate the number of days between two given dates.

I covered how you can calculate the total number of days between us start and the end date, or you can calculate only the working days using functions such as NETWORKDAYS and NETWORKDAYS.INTL.

I also covered how to calculate the number of working days when you’re in a part-time job, or you want to only calculate the number of specific days between the two dates (such as the number of Fridays or the number of Mondays between two given dates).

Other articles you may also like: