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.
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 |
DAYS function Syntax
DAYS(end_date, start_date)
DAYS function Arguments
| 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. |
DAYS function example
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:
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.
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
Functions in 'Date and Time' category
The DAYS function function is one of many 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