Many a time, while working with dates in excel, you may wish to calculate and count the number of days between two dates. For example, for counting the number of days someone took money on interest. Likewise, to know the number of days a student issued the book from the library. And the list is endless. Microsoft excel has an inbuilt function for this viz. DAYS function in Excel.
The usage of this function is very appreciable. In this tutorial, we would learn how to use the DAYS formula in excel, its syntax, arguments, practical examples, and usage.
- When to Use DAYS Function Of Excel
- Syntax and Arguments
- Points To Remember About DAYS Formula of Excel
- Examples to Learn DAYS Function in Excel
Here we go 😎
When to Use DAYS Function Of Excel
The Excel DAYS Formula finds the number of days between two dates. The two dates are given as input parameters of this function. It is one of the powerful date/time functions of Excel.
The formula returns the numeric value (representing days).
Syntax and Arguments
The below points explains the arguments of the excel DAYS function.
- end_date – In this argument, specify the ending date.
- start_date – In this argument, specify the starting date.
There is no optional argument required for the DAYS Formula in Excel.
Points To Remember About DAYS Formula of Excel
The following points should be kept in mind before using the DAYS Function.
- The function returns a negative answer if the start_date is smaller than end_date argument.
- The DAYS function returns #VALUE! error for the dates earlier than 1 Jan 1900. This is because excel perform calculattions on dates in the form of number. 1 Jan 1900 is interpreted as 1, 2 Jan 1900 as 2 and so on. So, any date before 1 Jan 1900 will be an invalid date.
- We can also pass the numerical value of a date to the function argument, In case the numerical value of a date is invalid, then the DAYS formula will return a #NUM! error.
- If the start_date and end_date argument is invalid (like 30 Feb 2021 or 32 Dec 2021), then the DAYS function will return a #VALUE! error.
Examples to Learn DAYS Function in Excel
Below are some of the practical examples to learn the usage of the DAYS formula of Excel.
Ex.1 – Simplest Example for DAYS Function of Excel
In this example, let us say we have the first and last date for which money was lent to the customers for a loan by a loan department.
Now, we want to find the number of days these customers took the money on interest.
To get the desired number of days, simply use the following DAYS Formula.
As a result, the function returns 24.
Explanation – Date in cell B2 is the end_date and A2 as the start_date. The function will first convert the dates into their corresponding numerical values in backend (you cannot see that on-screen). After that, it will find the difference between the two numerical date values which will be the result of the formula. The number of days between 25-1-2021 and 01-01-22021 is 24.
Copy the formula in cell C2 to other cells in column C or use the fill handle tool.
Ex. 2 – Dealing With Negative Result of Excel DAYS Formula
Many times, we might pass the function arguments such that the starting date is greater than the end date.
Below is the example.
In this example, the DAYS formula returns a negative value viz. -9.
Explanation – We have passed B2 as end_date and A2 as start_date. Here, the end_date is smaller than the start_date. When their corresponding numerical values are subtracted, it will lead to a negative result.
Solution – We can use the ABS Formula of Excel with the DAYS Formula. The ABS formula will make the result of DAYS function as a positive value, as shown below:
Ex. 3 – Passing Dates As Function Arguments For DAYS Function
In this example, instead of passing the cell references, we will pass the dates directly as the function arguments.
Let us say we want to find the number of days between 10-10-2002 and today’s date. To return the current date, let us use the TODAY function of Excel.
As a result, the formula returns the number of days as 6888.
Explanation – The function TODAY() is passed as the end_date (19-08-202). The start_date is set to “10-10-2002”. The number of days between the two dates is 6888 which is the required result.
The dates (without cell reference) are always enclosed within double quotes.
Thank you for reading 😉