Skip to main content

Calculate days between two dates in Excel (7 examples)

As an Excel user, you may run into a situation where you need to know how many days are between two dates in Excel. Or you want to count the days between today and a specific date. Or you wish to get the number of working days between two dates. This tutorial will introduce a few easy and quick methods to calculate the number of days between two dates in all three scenarios.
Calculate days between two dates
Using subtraction
Using Kutools to count days, weeks...
Using DAYS function
Using DATEDIF function
Count days between today and a date
Count workdays between two dates
Excluding weekends
Excluding weekends and holidays

doc calculate days between two dates 01

doc calculate days between dates 02

doc calculate days between two dates 05

Video: Count days between dates


Calculate days between two dates

Supposing you have the Start date in cell C2 and the End date in cell C3, you want to find out the number of days between the two dates. In this section, we will show you four ways to achieve your goal.

doc calculate days between two dates 04

Using subtraction

To count days between two dates, simply subtract the start date from the end date. Here is the generic formula:

=End_date - Start_date
Step 1: Input the subtraction formula

In cell C6, apply the following formula, then press the Enter button.

=C3-C2
Result

As you can see, there are 180 days between the two dates in cells C3 and C2.

doc calculate days between two dates 05

Notes:
  1. Excel stores dates as serial numbers that start on 1/1/1900, which is represented by the number 1. So, when you subtract one date from another, you are actually subtracting the numeric values of the dates.
  2. If the End_date is older than the Start_date, the returned result will be a negative integer. If you want to always get a positive result, you can use the below formula:
    =ABS(End_date - Start_date)

Using Kutools to count days, weeks, months, and years between dates

With the Date & Time Helper feature of Kutools for Excel, you can also calculate days between dates. What’s more, this feature is more powerful because it offers more options, like calculating weeks, months, years, and so on between two dates. The screenshot below shows how to count days between dates step by step, and the more options you can also calculate.

doc calculate days between two dates 07

After enabling the feature by clicking Kutools > Formula Helper > Date & Time Helper, please do as follows:

  1. Choose Difference in the Type section;
  2. Select cell C2 and cell C3 separately in Date1 and Date2 textbox;
  3. Choose day from the Output result type drop-down list ;
  4. Click OK.
Notes:
  1. To apply the Date & Time Helper feature, you should install Kutools for Excel first. Kutools for Excel offers a 30-day free trial with no limitations, go to download and install it now!
  2. Apart from calculating the difference between two dates, the Date & Time Helper feature also supports other date and time calculations. To add the date and time, check the Add option in the Type section. To calculate age based on a given date, check the Age option in the Type section.

Using DAYS function

Another way for getting the number of days between two dates is by using the DAYS function, which is introduced in Excel 2013. Here is the generic formula of the DAYS function:

=DAYS(end_date, start_date)
Step 1: Input the DAYS formula

In cell C6, apply the following formula, then press the Enter button.

=DAYS(C3,C2)
Result

180 days are between the two dates in cells C3 and C2.

doc calculate days between two dates 11

Note: If the End_date is older than the Start_date, the returned result will be a negative integer. If you want to always get a positive result, you can use the below formula:
=ABS(DAYS(end_date, start_date))

Using DATEDIF function

The fourth method of counting numbers of days between two dates is using the DATEDIF function. Here is the generic formula for calculating day difference between two dates:

= DATEDIF(start_date, end_date, "d")
Step 1: Input the DATEDIF formula

In cell C6, apply the following formula, then press the Enter button.

=DATEDIF(C2,C3,"d")
Result

180 days are between the two dates in cells C3 and C2.

doc calculate days between two dates 12

Notes:
  1. Unlike the other 3 methods, the DATEDIF formula doesn’t support subtracting a newer date from an older date. When the End_date is older than the Start_date, a #NUM! error will be returned.
  2. Different from the subtraction formula and DAYS function which can only count days between days, DATEDIF also can calculate months or years between two dates.
  3. DATEDIF is not included in the list of functions in Excel, which means it is an undocumented function. This requires you to manually input all the arguments to create a DATEDIF formula in your worksheet.

Count days between today and a date

If you want to calculate the number of days between today and a specific date, Excel’s built-in TODAY function can help you get the job done easily and quickly. Here are the two generic formulas to count days between today and another date with TODAY function:

To calculate the number of days between a past date and today:

=TODAY() - past_date

To calculate the number of days between a future date and today:

=Future_date - TODAY()

In this case, you have a future date in cell C2, and today is 15-Mar-2023. You need to calculate the number of days between today and this date, please do as follows.

doc calculate days between two dates 13

Step 1: Input the formula

In cell C5, apply the following formula, then press the Enter button.

=C2-TODAY()

doc calculate days between two dates 14

Step 2: Format result as general

Select the result cell, click the Home tab, go to the Number group, then select General from the Number Format drop-down list.

doc calculate days between two dates 12

Note: Or on the Home tab, click the Dialog Box Launcher doc calculate days between two dates 13 next to Number. Then click General in the Category box.

Result

As you can see, there are 108 days between the today and the date in cell C2.

doc calculate days between two dates 17

Note: The TODAY function will give the current date exactly. So, when a specific date is fixed, you will find that the number of days returned in the worksheet varies because the TODAY function changes from day to day.


Count working days between two dates

From time to time, we may need to calculate the number of days without the weekends. Here we will show you how to count working days between two dates with the help of NETWORKDAYS function in two situations. One situation is counting workdays without additional holidays provided. The other situation is counting workdays with additional holidays provided.

Here is the generic formula of NETWORKDAYS function:

=NETWORKDAYS(start_date, end_date, [holidays])

Excluding weekends

For example, you have the Start date in cell C2 and the End date in cell C3, you want to find out the number of working days between the two dates regardless of the holidays.

doc calculate days between two dates 18

Step 1: Input the NETWORKDAYS formula

In cell C6, apply the following formula, then press the Enter button.

=NETWORKDAYS(C2,C3)
Result

You can see there are 130 working days between the two dates, not considering holidays.

doc calculate days between two dates 19

Excluding weekends and holidays

Unlike the example above, you also have the additional holidays listed in the data range C5:C8, and you need to calculate the number of working days between the two dates with the holidays provided.

doc calculate days between two dates 20

Step 1: Input the NETWORKDAYS formula

In cell C6, apply the following formula, then press the Enter button.

=NETWORKDAYS(C2,C3, E3:E7)
Result

You can see there are 125 working days between the two dates, considering the holidays provided.

doc calculate days between two dates 21

Note: In most of the world, the weekend is Saturday and Sunday. Then the NETWORKDAYS function is the exact function you need. But if your weekend is not Saturday and Sunday (like Sunday only), you should use the NETWORKDAYS.INTL function, which allows you to specify which days of the week should be considered weekends.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Ifdate Excel: Calculate the difference between two dates

As the name suggests, the DATEIF function in excel is designed to calculate the difference between two dates.

IfExcel Date is one of the few undocumented functions in Excel, and because it is "hidden" you won't find it in the Formula tab, nor will you get any clues about what arguments to enter when you start typing the function name into the formula. That is why it is important to know the complete syntax of sifecha excel, to be able to use it in your formulas. see link
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations