How to Use DAYS Function in Excel (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

overview image of how to use 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.

how to use DAYS function in Excel

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.

enter the function

  • 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.

get estimated time


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.

show negative 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.

Combine DAYS & ABS Functions to Find 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.

show the output


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.

selected dates as formatted 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.

Use of DAYS Function to Handle Date Formatted as Text

  • 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.

Drag the Fill Handle icon


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.

Combine VLOOKUP and DAYS Functions


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.

Use of DAYS & TODAY Functions to find 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.

drag the Fill Handle icon

 


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.

drag the Fill Handle icon


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.

Calculate Working Days Between Two Dates


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.

Date Out of Valid Range

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

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!


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo