This article explains the syntax and the use of DAYS function in Excel.
The Microsoft Excel DAYS function returns the number of days between two supplied dates. The DAYS function was first introduced in Excel 2013. So, if you are still using an earlier version of Excel that does not support the DAYS function, try using either the old method to calculate days or DATEDIF function.
The use of DAYS function is simple, is finding the number of days between two dates. You can also use DAYS function with other excel dates function. For example, when you want to calculate the number of days elapsed in a year or remaining days in a month/year.
DAYS (end_date, start_date)
- end_date: A required and valid excel end date to where you want to calculate the days. It can either be supplied as a reference to a cell, the result of functions or formulas or directly to DAYS function as text representations of date.
- start_date: Required. A valid excel date (start date), from where you want to start calculating the days. You can supply start date as text representation of date, reference to a cell containing valid excel date or function or formulas returning a serial number of a valid date.
Tip: Make sure ‘dates’ are enclosed in quotation marks when supplied directly into the DAYS function.
Examples: DAYS Function
Let’s explore a few examples of DAYS function to see how to use the DAYS function in Excel.
In the examples above, the DAYS function in cell C2 returns the days as 0 because of the start date and end date both same, i.e. May 15, 2018.
In C7, the supplied dates fall in the leap year, so DAYS calculate the days automatically and return the actual days between the start date and end date as 366.
DAYS with Excel ABS Function
Suppose the end date is a date before the start date and in such scenerios, the DAYS function will return a negative value.
For example, the function, =DAYS (“15-May-18”, “1-May-18”) returns the days value as -14.
In such cases, you can use DAYS along with ABS function that returns the absolute value of a number, without a sign.
So, the formula, =ABS (DAYS (“15-May-18”, “1-May-18”) ) returns the days value as 14 only this time.
Returns the Elapsed Days in a Year
Let’s say, you want to find out how many days have passed this year. You can use DAYS with other date functions as shown below.
=DAYS (TODAY(), DATE(2018, 1, 1) )
Note: At the time of writing this article, the current date is 15th May 2018.
I tried my best to shed some light on the use of DAYS function. I hope you find this information helpful. Following are some more useful notes about this function.
Additional Notes: –
- #VALUE! error occurs if one or both arguments are the text representations of dates, but not recognized as valid dates.
- DAYS returns #NUM! errors if either date arguments are numeric values but fall outside the valid range of dates.
- Both start_date and end_date arguments must be a valid date ranging from 1/1/1900 to 12/31/9999.
- Microsoft interprets dates supplied as text differently, depending on the date systems and date settings on your computer. So, it is advisable to either use a cell containing date or formulas returning a serial number of valid excel date.
- To learn more about DAYS function, visit Microsoft Office Website.