How to Calculate the Number of Days Between Two Dates in Excel

Watch Video – Calculate the Number of Workdays Between Two Dates

Excel has some powerful functions to calculate the number of days between two dates in Excel. These are especially useful when you’re creating Gantt charts or timelines for a proposal/project.

In this tutorial, you’ll learn how to calculate the number of days between two dates (in various scenarios):

Calculating the Total Number of Days Between Two Dates in Excel

Excel has multiple ways to calculate the days between two dates.

Using the DAYS Function

Excel DAYS function can be used to calculate the total number of days when you have the start and the end date.

You need to specify the ‘Start Date’ and the ‘End Date’ in the Days function, and it will give you the total number of days between the two specified dates.

For example, suppose you have the start date is in cell B1 and End Date is in cell B2 (as shown below):

Start date and end date in Excel

The following formula will give you the total number of days between the two dates:

=DAYS(B2,B1)

DAYS Formula to get the Number of Days

Note that you can also manually specify the dates in the Days function by putting it in double-quotes. Just make sure these dates in double-quotes is in an accepted date format in Excel.

Days function gives you the number of days between two dates. This means that if the dates are 1 Dec 2017 and 2 Dec 2017, it will return 1. If you want both the days to be counted, you need to add 1 to the result of Days function. You can read more about the Days function here.

Using the DATEDIF Function

DATEDIF function (derived from Date Difference) also allows you to quickly get the number of days between two dates. But unlike the DAYS function, it can do more than that.

You can also use the DATEDIF function to calculate the number of months or years that have elapsed in the two given dates.

Suppose you have the below dataset and you want to get the number of days between these two dates:

Start date and end date in Excel

You can use the below DATEDIF formula to do this:

=DATEDIF(B1,B2,"D")

The above DATEDIF formula takes three arguments:

  • The start date – B1 in this example
  • The end date – B2 in this example
  • “D” – the text string that tells the DATEDIF function what needs to be calculated.

Also note that unline the other Excel functions, when you type the DATEDIF function in Excel, it will not show the IntelliSense (the autocomplete option that helps you with the formula arguments).

If you only want to calculate the number of days between two given dates, then it’s better to use the DAYS function. DATEDIF is more suited when you want to calculate the total number of years or months that have passed in between two dates.

For example, the below formula would give you the total number of months between the two dates (in B1 and B2)

=DATEDIF(B1,B2,"M")

Similarly, the below formula will give you the total number of years between the two dates:

=DATEDIF(B1,B2,"Y")

You can read more about the DATEDIF function here. One of the common uses of this function is when you need to calculate age in Excel.

Number of Working Days Between Two Dates in Excel

Excel has two functions that will give you the total number of working days between two dates and will automatically account for weekends and specified holidays.

Let’s first quickly have a look at NETWORKDAYS Function syntax and arguments.

Excel NETWORKDAYS Function – Syntax & Arguments

=NETWORKDAYS(start_date, end_date, [holidays])

  • start_date – a date value that represents the start date.
  • end_date – a date value that represents the end date.
  • [holidays] – (Optional) It is a range of dates that are excluded from the calculation. For example, these could be national/public holidays. This could be entered as a reference to a range of cells that contains the dates, an array of serial numbers that represent the dates, or a named range.

Let’s first look at an example where you want to calculate the number of working days (business days) between two dates with Saturday and Sunday as weekends.

Dates data set where days needs to be calculated between two dates

To calculate the number of working days (Column D) – when the start date, end date, and holidays are specified – use the below formula in D3 and copy for all cells:

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

networkdays formula result

 

This function works great in most cases, except the ones where the weekends are days other than Saturday and Sunday.

For example, in middle-eastern countries, the weekend is Friday and Saturday, or in some jobs, people may have a six-day workweek.

To tackle such cases, Excel has another function – NETWORKDAYS.INTL (introduced in Excel 2010).

Before I take you through the example, let’s quickly learn about the syntax and arguments of Excel NETWORKDAY INTERNATIONAL function

Excel NETWORKDAYS INTERNATIONAL Function – Syntax & Arguments

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

  • start_date – a date value that represents the start date.
  • end_date – a date value that represents the end date.
  • [weekend] – (Optional) Here, you can specify the weekend, which could be any two days or any single day. If this is omitted, Saturday and Sunday are taken as the weekend.Networkdays International formula syntax and weekend options
  • [holidays] – (Optional) It is a range of dates that are excluded from the calculations. For example, these could be national/public holidays. This could be entered as a reference to a range of cells that contains the dates or could be an array of serial numbers that represent the dates.

Now let’s see an example of calculating the number of working days between two dates where the weekend days are Friday and Saturday.

Suppose you have a dataset as shown below:

networkdays function

To calculate the number of working days (Column D) with the weekend as Friday and Saturday, use the following formula:

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

The third argument in this formula (the number 7) tells the formula to consider Friday and Saturday as the weekend.

networkdays intl formula result

Number of Weekends Between Two Dates in Excel

We can use the NETWORKDAYS function to calculate the number of weekends between two dates.

While the Networkdays function calculates the number of working days, we can also use to get the number of weekend days between two dates.

Suppose we have a dataset as shown below:

Dataset to calculate the number of weekend days in Excel

Here is the formula that will give you the total number of weekends days between the two dates:

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

weekend formula - to give the number of weekend days between two dates

Number of Work Days in a Part-time Job

You can use Excel NETWORKDAYS.INTL function to calculate the number of workdays in a part-time job as well.

Let’s take an example where you are involved in a project where you have to work part-time (Tuesday and Thursday only).

Calculate part time working days in Excel - Dataset

Here is the formula that will get this done:

=NETWORKDAYS.INTL($B$3,$C$3,"1010111",$E$3:$E$7)

NETWORKDAYS International formula to get the part time working days

Note that instead of choosing the weekend from the drop-down that’s inbuilt in the function, we have used “1010111” (in double quotes).

  • 0 indicates a working day
  • 1 indicates a non-working day

The first number of this series represents Monday and the last number represents Sunday.

So “0000011 would mean that Monday to Friday are working days and Saturday and Sunday are non-working (weekend).

With the same logic, “1010111” indicates that only Tuesday and Thursday are working, and rest 5 days are non-working.

In case you have holidays (which you don’t want to get counted in the result), you can specify these holidays as the fourth argument.

Number of Mondays Between Two Dates

To find the number of Mondays between two dates (or any other day), we can use the same logic as used above in calculating part-time jobs.

Suppose you have a dataset as shown below:

Start date and end date to get the number of Mondays

Here is the formula that will give you the number of Mondays between the two dates:

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

Using Networkdays INTL function to get the number of Mondays

In this formula, ‘0’ means a working day and ‘1’ means a non-working day.

This formula gives us the total number of working days considering that Monday is the only working day of the week.

Similarly, you can also calculate the number of any day between two given dates.

You May Also Like the Following Tutorials:

  • Abdul Malick says:

    Guys!!! Please tell me some one days calculator for android mobiles and iphone mobiles.

  • Abdul Malick says:

    Please tell me some android application names for days calculator.

  • Agnes says:

    https://uploads.disquscdn.com/images/2aceda413b6251203844e8115b328a44b0b73d3dd2a761e26870bad04ac8e809.png

    I would like to calculate the number of months in the attached table. Can anyone help me?

  • John Plant says:

    Hi Sumit, Very informative. Thank you. Especially the use of the binary day-of-week parameter. That could be handy.

  • anil says:

    I have a excel sheet and i want to send alert mail as a list of customers whose Software LIC are going to expire on so and so date.

  • Thanks for sharing the informative post. Keep it up.

  • Md Nasir says:

    Dear Sumit
    Good Day!

    very nice your concept for me

  • Ashwell Makan says:

    Hi Sumit, thanks for the tutorial, very informative. How do I calculate time in hours between dates. If you want to calculate the working hours from 15.00 in afternoon until 09’00 the next morning? If your working hours is 08h00 to 17h00, the working hours would be 3 hours in total ( 2 from previous day nd 1h for present day). Any formula to calculate the total working time between dates?

    • Hey.. when you have days changing, you need to use the date as well as time to make the entry. Once you have two dates with time, just subtract first from the later.

  • adil says:

    I need to calculate the # of days between 2 dates, but excluding Sundays, so only 7 days in a week, Including Holidays.

  • Mary says:

    Hello
    I need to calculate the # of days between 2 dates, but excluding Sundays, so only 6 days in a week. Anyone know a function/formula that can accomplish this?
    Thank you

    • Hello Mary.. You can use the below formula:
      =NETWORKDAYS.INTL(start date,end date,11)

  • >