How to use the DAYS function
What is the DAYS function?
The DAYS function calculates an integer that represents the number of days between two dates.
There is, actually, an easier way to calculate the number of days between two dates. Simply subtract C3 by B3 like this: = C3-B3 and you will get the number of days.
The DATEDIF function is also able to calculate the number of days between two dates.
Table of Contents
1. Introduction
What are dates in Excel?
Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.
For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.
This allows dates to easily be formatted to display in many date/time formats like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.
You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.
Related functions
| Excel Function | Description |
|---|---|
| DAY(date) | Returns the day of the month (1-31) |
| DAYS(end_date, start_date) | Returns the number of days between two dates |
| NETWORKDAYS(start_date, end_date) | Returns the number of workdays between two dates |
| DATEDIF(start_date, end_date, unit) | Returns the time between two dates in specified units like years, monts, etc |
| EDATE(start_date, months) | Returns the date that is the indicated number of months before or after start_date |
| EOMONTH(start_date, months) | Returns the last day of the month before or after start_date by months |
| NETWORKDAYS.INTL(start_date, end_date, weekend) | Returns workdays between dates with custom weekend parameters |
| WORKDAY(start_date, days) | Returns a date days away from the start_date, skipping weekends |
2. Syntax
DAYS(end_date, start_date)
| end_date | Required. The end date of the date range you want to calculate. |
| start_date | Required. The start date of the date range you want to calculate. |
3. Example 1
This example shows the DAYS function in cell D3, in the image above, calculating the difference in days between the specified cells C3 and B3.
Formula in cell D3:
The DAYS function calculates 1 day between 1/1/2030 and 1/2/2030. Why, you may wonder? There are two days in that date range? It depends how you calculate. 1/1/2030 is 47484 and 1/2/2030 is 47485. 47485 - 47484 equals 1.
This is because if you calculate the end date inclusive or not. If you want to include the end date in the calculation then use this:
Formula in cell D3:
4. Example 2
You are the project manager for a new product launch, and you have identified the following milestones:
- Project Kickoff - January 10, 2026
- Design Completion - February 20, 2026
- Prototype Development - April 1, 2026
- Testing and Quality Assurance - May 15, 2026
- Launch Date - June 30, 2026
Use the DAYS function in Excel to calculate the number of days between each milestone. For example,
- How many days are between the Project Kickoff and Design Completion?
- How many days are between Design Completion and Prototype Development?
The image above shows a spreadsheet that lets you select two milestones and calculate the number of days between the given milestones.
Cell B10 contains a drop down list that expands when you select the cell. It allows you to select the specified milestones in cells B3:B7 using the drop down list. Cell C10 also contains a drop down list so you can pick another milestone. The formula in cell C13 uses these milestones to get the correct corresponding dates and then calculate the number of days between.
Formula in cell C13:
Cell B10 contains Project Kickoff and C10 contains Design Completion, the number of days between these milestones are 46. Here is a breakdown of the formula:
- C3:C7 is a range of cells that contains dates.
- B3:B7 is a range of cells that contains milestone names.
- C10 and B10 are cells that contain specific values that are used to filter the data.
The formula is using the FILTER function to select the dates from C3:C7 that correspond to the values in B3:B7 that match the values in C10 and B10.
- FILTER(C3:C7,B3:B7=C10) selects the dates from C3:C7 where the corresponding value in B3:B7 is equal to the value in C10.
- FILTER(C3:C7,B3:B7=B10) selects the dates from C3:C7 where the corresponding value in B3:B7 is equal to the value in B10.
- The DAYS function then calculates the number of days between the two dates that are returned by the FILTER functions.
This formula can be used to calculate the number of days between two specific milestones where the milestones are identified by their names in column B, and the dates are in column C. The values in cells C10 and B10 can be used to select the specific milestones to calculate the days between.
For example, if C10 contains the value "Design Completion" and B10 contains the value "Prototype Development", the formula would calculate the number of days between the Design Completion date and the Prototype Development date.
5. DAYS function tips and tricks
Why are dates sometimes stored as text in Excel?
Excel tries to identify values as text, numbers, Boolean values, dates, and time automatically but may fail in rare occasions. This may happen when you import data from the internet, databases, text files, and other sources. You can convert the dates to Excel dates if you like but it can be a tedious and time consuming task.
There is no need to convert text dates to Excel dates, the DAYS function handles text dates properly.
6. DAYS function not working
The image above shows two dates containing a dot as a delimiting character, this makes Excel return an error.
The DAYS function returns #VALUE! error if a date is not recognized, here is a formula to fix this problem: DATEVALUE function
'DAYS' function examples
The following article has a formula that contains the DAYS function.
Functions in 'Date and Time' category
The DAYS function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form