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
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.
The DAYS function returns the number of days between two dates.
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:
#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”.
Note that the function wants the ending date first, then the starting date.
Below are a few simple examples of the function working well in a spreadsheet.
|Number of days between ||366|
|Number of days between ||1,494|
|Number of days between ||-2,581|
Next, let’s look at a few examples of the function breaking down.
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.