The DAYS function is a simple date function that calculates the number of days between two dates.
The function returns a negative number if the start and end dates are reversed.
Calculating the difference between two days with the - operator or MINUS function produces the same result unless the end_date or start_date contain times. Using subtraction can result in a fraction of a day, while using the DAYS function ignores times and always returns whole numbers. Use the TIMEDIF Google Sheets add-on for fully formatted durations between two times, such as “2 years 1 week 5 hours.”
Before reading the examples below, ensure you know how dates and times work, as a large part of using date functions correctly is understanding what makes up a valid date. If you do not provide valid dates, the formula will error.
Contents
Purpose
The DAYS function returns the number of days between two dates.
Related Functions
DATEDIF – Calculates the time between two dates in years, months, days, or a combination thereof. Use this for a more flexible alternative.
MINUS – Subtracts one value from another. You can use this function with dates.
NETWORKDAYS – Calculates the number of workdays between two dates.
NETWORKDAYS.INTL – Calculates the number of workdays between two dates. NETWORKDAYS.INTL can exclude holidays if you provide them. This function is a more flexible version of NETWORKDAYS.
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Errors
#NUM – The inputs are numbers but are not valid dates. If you used the 35th day of November, “11/35/2018”, your dates would be invalid.
#VALUE! – The inputs don’t convert to a number such as “The other day” or “Yester-yester-day”.
Syntax
=DAYS(end_date,start_date)
Note that the function wants the ending date first, then the starting date.
Video Explanation
Examples
Below are a few simple examples of the function working well in a spreadsheet.
| Formula | Description | Result |
=DAYS("7/31/2017","7/30/2016") | Number of days between 7/30/2016 and 7/31/2017 | 366 |
=DAYS("1/7/2020","12/5/2015") | Number of days between 12/5/2015 and 1/7/2020 | 1,494 |
=DAYS(C3,"3/5/2001") | Number of days between 3/5/2001 and 2/9/1994 (assuming that 2/9/1994 is in cell C3) | -2,581 |
Next, let’s look at a few examples of the function breaking down.
| Formula | Description | Result |
=DAYS("12/1/2015","The other day")
|
"The other day" cannot be parsed as a valid date.
| #VALUE! |
=DAYS("12/1/2015","11/35/2014")
|
"11/35/2014" cannot be parsed as a valid date.
| #VALUE! |
=DAYS("12/1/2015",33333333)
| 33333333 is a numeric value that falls outside the range of valid dates. See the template, as this error only occurs when 33333333 is in a cell reference. | #NUM! |
For output including years and months, consider using the DATEDIF function.
Live Examples in Sheets
Go to this spreadsheet for examples of the DAYS function that you can study and use anywhere you would like.
