Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Number of Days Between Two Dates in Excel

There might be a lot of reasons that you need to calculate the number of days between two dates in Excel or the number of weeks, months, and years between two dates in Excel. If you have been given a task which you have to complete within a fixed time, knowing the number of workdays or weekends is a smart thing. In this article on how to calculate number of days between two dates in Excel, we will demonstrate different formulas and functions for calculating the number of days, weeks, years, weekends and workdays in Excel.


Download Practice Workbook


6 Ways to Calculate Number of Days Between Two Dates in Excel

Excel offers various methods to calculate days between two dates. To use these formulas, we have made a dataset named Joining and Contract Renewal Date of Some Employees. It has column headers as Name of Employee, Joining Date, and Contract Renewal Date. The dataset is like this.

number of days between two dates excel


1. Using Subtraction Formula

Subtraction is quite a simple method to calculate days between two dates. In this method, you need to subtract the two dates directly in Excel. To execute the method, please follow the steps below.

Steps:

  • Firstly, write the formula in the E5
=D5-C5

Here, D5 and C5 are the Contract Renewal Date and Joining Date of Jane respectively.

Using Subtraction Directly to Calculate Number of Days between Two Dates in Excel

  • Secondly, press ENTER to find the output as 23.
  • Thirdly, use Fill Handle by dragging the cursor downwards while holding the cursor on the right-bottom corner of the E5 cell like this.

Using Subtraction Directly to Calculate Number of Days between Two Dates in Excel

  • Eventually, we’ll get all the outputs like this.

Read More: How to Subtract Dates in Excel to Get Years (7 Simple Methods)


2. Utilizing DATEDIF Function

We can use the DATEDIF function to find years, months, or days between two dates. However, we’ll use it here to find days.

  • To show this, firstly write the formula in the E5 cell like this.
=DATEDIF(C5,D5,”D”)

Utilizing DATEDIF Function to Calculate Number of Days between Two Dates in Excel

  • Secondly, press ENTER and use Fill Handle to get all the outputs like this.

Read More: How to Count Days from Date to Today Automatically Using Excel Formula


3. Applying DAYS Function

The DAYS function is one of the core functions to calculate days between two dates.

  • We can write the following formula in the E5 cell like this.
=DAYS(D5,C5)

Applying DAYS Function to Calculate Number of Days between Two Dates in Excel

  • Similarly, press ENTER, and after that, by using the Fill Handle we get all other outputs like this.

Read More: Excel Formula to Calculate Number of Days Between Today and Another Date


4. Using TODAY Function

We can use the TODAY function to calculate days. The TODAY function automatically gives the date of today and it changes according to the change of today’s date. Later, we need to use the DATEDIF function to calculate days.

Steps :

  • We can write the TODAY function in the formula like this.
=TODAY()

Using TODAY Function to Calculate Number of Days between Two Dates in Excel

  • If we press ENTER and then use Fill Handle, we’ll find today’s date in the output cells.

Using TODAY Function to Calculate Number of Days between Two Dates in Excel

  • Secondly, we need to write the following formula in the F5
=DATEDIF(C5,D5,"D")

Here, D refers to Date. We can also use M or Y to find Months or Years respectively.

Using TODAY Function to Calculate Number of Days between Two Dates in Excel

  • Lastly, press ENTER and use Fill Handle to get all the outputs on days like this.

Read More: Calculate Number of Days between Two Dates with VBA in Excel


Similar Readings


5. Using NOW Function

We can also use the NOW function instead of the TODAY function. Basically, these two functions work in the same way and give the same output.

Steps:

  • Initially, we can use the NOW function in the D5 cell like this.
=NOW()

Using NOW Function to Calculate Number of Days between Two Dates in Excel

  • Eventually, press ENTER.
  • Similarly as before, use the Fill Handle.

Using NOW Function to Calculate Number of Days between Two Dates in Excel

  • Secondly, we need to write the DATEDIF function in the E5 cell like this.
=DATEDIF(C5,D5,"D")

Using NOW Function to Calculate Number of Days between Two Dates in Excel

  • Finally, use the Fill Handle after pressing ENTER to get the outputs like this.

Read More: How to Apply Excel Formula to Count Days from Date to Today


6. Applying DATEDIF and TODAY Functions

  • We can use the combination of DATEDIF and TODAY functions instead of using those individually.
    Firstly, write the formula in the D5 cell like this.
    =DATEDIF(C5,TODAY(),"D")

Applying DATEDIF and TODAY Functions to Calculate Number of Days between Two Dates in Excel

  • Lastly, as before, press ENTER and use the Fill Handle.

Read More: Excel Formula to Count Days from Date (5 Easy Methods)


Calculate Number of Days Between Two Dates Excluding Weekends

The NETWORKDAYS function is used when we need to calculate the working days between two dates. The NETWORKDAYS function automatically removes the vacations from the calculation. You can easily find the number of total working days between 2 dates by using the NETWORKDAYS and NETWORKDAYS.INTL functions. The difference between these two functions is that the NETWORKDAYS is designed to exclude Saturday and Sunday as weekend days by default but in the NETWORKDAYS.INTL functions you can define the weekends.

  • We can show this formula by writing in the E5 cell like this.
=NETWORKDAYS(C5,D5)

Calculate Number of Days Between Two Dates Excluding Weekends

  • Similarly, as before, press ENTER Then use the Fill Handle to get the outputs.
  • Here, we can see that in the outputs the holidays are eliminated from calculations.

Read More: How to Calculate Working Days Excluding Sundays in Excel


Add/Subtract Days Excluding Weekends

We can add/subtract days excluding weekends by using the NETWORKDAYS.INTL function.
Here, in the D Column we have put the Days Need to be Added with the Joining Date to find out the Contract Renewal Date of the employees.

  • Firstly, write the formula like this.

=WORKDAY.INTL(C5,D5,7)

Here, 7 refers to the type of weekend we need to take into account. In the formula 7 refers to Friday and Saturday as weekend.

Add/Subtract Days Excluding Weekends

  • Secondly, press ENTER and use Fill Handle to find all the outputs.

  • For defining the weekends in the NETWORKDAYS.INTL formula we can follow the following table.

Read More: How to Calculate Working Days in a Month in Excel (4 Easy Ways)


Calculate Number of Weekends

We can calculate number of weekends by using the combination of SUM, INT and WEEKDAY functions. To show this write the formula in the E5 cell like this.

=SUM(INT((WEEKDAY(C5-{3,7})+D5-C5)/7))

Calculating Number of Weekends

  • Eventually, press ENTER and use the Fill Handle.


Conclusion

We can find days between two days easily if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

2 Comments
  1. I am working in ship brokers company . We have laytime calculation . Will you have any sheet for that?

    • Not right now. I will update when I will have one. Or if you have any specifications, let me know. I will make one for you.
      Thanks and regards
      Kawser

Leave a reply

ExcelDemy
Logo