In the Excel sheet, we store the progress of various work and projects. To track those projects or get any estimated time you can use the DAYS function. The Excel DAYS function is used to calculate the number of days between two dates.
The article covers a comprehensive range of topics, including the syntax and usage of the DAYS function, handling different date formats, incorporating additional functions for advanced calculations, and troubleshooting common errors. Furthermore, we will demonstrate practical examples and provide useful tips to enhance your understanding and mastery of the DAYS function.
So, if you’re ready to unlock the full potential of Excel’s DAYS function and revolutionize your data analysis, let’s dive into the article and discover how to wield this powerful tool with confidence.
Below is the overview image of how to use the DAYS function in Excel.
Introduction of DAYS Function: Summary & Syntax
Summary
The DAYS function returns the number of days between two dates.
Syntax
DAYS (end_date, start_date)
Arguments
| Â Â Arguments | Â Â Â Required/Optional | Explanation |
|---|---|---|
| end_date | Required | It is a date in a format where Excel recognizes it as a date-time serial number or text. |
| start_date | Required | It is a date in a format where Excel recognizes it as a date-time serial number or text. |
Return Value
The DAYS function returns a number that represents the total days between two dates.
Version
The DAYS function is available for Excel 2013 and later versions.
User Input Types
The DAYS function can show different results based on different user inputs.
The user may input different formats of dates as shown below.
The number of days between two dates, which is displayed in cell C6, is returned by the DAYS function.
You can add 1 to the result, which is displayed in cell C7, to make the end date part of the count.
The DAYS function can handle text values. In case your dates are formatted as text you can use the DAYS function to get the number of days between two text-formatted dates.
How to Use DAYS Function in Excel: 5 Suitable Examples
In this section, we will demonstrate 5 suitable examples to show the use of the DAYS function. This section provides extensive details on these examples. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Using Excel DAYS Function to Get Estimated Time
To know the number of days to completion for any particular work or project you can use the DAYS function.
Here, We want to know the total number of days needed to complete the project stream Ray from its Start Date and End Date using the DAYS function.
- Firstly, we have to type the following formula.
=DAYS(D5,C5)
Here, the D5 cell has the end date and the C5 cell has the start date.
- Then, press Enter.
- As a result, the DAYS function will return the number of days between the two dates.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Therefore, you can get the number of days it has taken to complete other projects as well.
2. Combine DAYS & ABS Functions to Find Project Completion Date
Here, we will demonstrate how to combine DAYS & ABS functions to find project completion dates in Excel.
While finding total days between two days, the DAYS function may return a negative value if you place the earlier date as end_date and the later date as start_date.
- For example, we have typed the following formula.
=DAYS(C5,D5)
Here, the C5 cell has the Start Date but, in the formula, we put the start date as end_date and the D5 cell has the End Date but in the formula, we put it as start_date.
- Next, press Enter.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- As a result, the DAYS function will return the negative number of days between the two dates.
To avoid negative values, you may use the ABS function with the DAYS function, then you will need to worry about the recent and end date of a project.
- Now, we have typed the following formula.
=ABS(DAYS(C5,D5))
Here, the C5 cell has the Start Date but, in the formula, we put the start date as end_date and the D5 cell has the End Date but, in the formula, we put it as start_date. Then, using the ABS function to return the absolute value while ignoring the negative sign.
- Next, press Enter.
- Thus, the DAYS function will return the project completion date.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Therefore, you can get the number of days it has taken to complete other projects also.
3. Use of DAYS Function to Handle Date Formatted as Text
The DAYS function can handle text values. In case your dates are formatted as text so you can use the DAYS function to get the number of days between two text-formatted dates.
Here, we formatted the dates as text.
- Now, we have typed the following formula.
=DAYS(D5,C5)
Here, the D5 cell has the end date and the C5 cell has the start date.
- Next, press Enter.
- Therefore, the DAYS function will return the number of days between the two dates.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Therefore, you can get the number of days it has taken to complete other projects.
4. Combine VLOOKUP and DAYS Functions to Get the Number of Days Between Two Dates
You can use the VLOOKUP function with the DAYS function to get the number of days between two dates.
The VLOOKUP function will lookup any particular value then the DAYS function will return the number of days between the lookup date and the date you want to use.
- Now, we have typed the following formula.
=DAYS(VLOOKUP(F5,C5:D11,1, FALSE),D5)
Here, in the VLOOKUP function, I have taken the F5 cell as lookup_value, taken the range B5:D11 as table_array, used 1 as col_index_num then used FALSE as range_lookup to get the Exact Match. Finally, it will return the value I’m looking for.
Then the DAYS function will return the total number of days between the looked_up date which is cell F5 and the date of the C5 cell.
Here, the D5 cell has the end date and the C5 cell has the start date.
- Next, press Enter.
- Hence, the DAYS function will return the number of days between the two dates.
5. Use of DAYS & TODAY Functions to find Total Days from Today
You can use the TODAY function within the DAYS function to get updates on any work or any project.
- Now, we have typed the following formula.
=DAYS(TODAY(),C5)
Here, from the TODAY function will get the date of today and I used it as end_date
Then, use the C5 cell as start_date in the DAYS function.
- Next, press Enter.
- Thus, the DAYS function will return the number of total days from today.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Therefore, you can get the number of total days from today.
Calculate Number of Days Between Dates in Excel without Days Function
You can use the DATEDIF function to calculate the number of days between two dates in Excel.
- Now, we have typed the following formula.
=DATEDIF(C5, D5, "d")
- Next, press Enter.
- Thus, the DATEDIF function will return the number of days between two dates in Excel.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Therefore, you can get the number of days between the two dates.
How to Calculate Working Days Between Two Dates in Excel
You can use the NETWORKDAYS function to calculate the number of working days between two dates in Excel.
- Now, we have typed the following formula.
=NETWORKDAYS(C5, D5)
- Next, press Enter.
- Then, drag the Fill Handle icon to fill the other cells with the formulas.
- Thus, the function will return the number of working days between two dates in Excel.
Known Errors of DAYS Function in Excel
🔺 String Not Recognized as Date
If you use dates as String (intentionally or unintentionally), you will see the #VALUE error.
🔺 Date Out of Valid Range
Using a date outside the valid date, you will see the #NUM error.
The valid date range dates from 1900 to 10,000 years.
Here, all dates are out of the valid range that’s why it is showing an error.
🔺 Misspelling Function Name
Download to Practice
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.
Conclusion
That’s the end of today’s session. I believe you will be able to use the DAYS function in Excel as of now. Through this article, you will learn 5 suitable examples of using the DAYS function with different applications in Excel. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Keep learning new methods and keep growing!
