Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Excel Days Function
2.1.
EXAMPLES
2.2.
Points to Note
3.
Excel Datedif Function
3.1.
Time Units
3.2.
Examples
3.3.
Points to Note
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

Days and DATEDIF Function

Author Komal Shaw
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

The Excel Days function is a Date/Time Function. It tells the count of the number of days between a start date and the end date.

The Excel Datedif function is also a Date/Time Function. It returns the count of the number of days, months, or years between two given dates.


Excel Days Function

Let there be a starting date A1 and an ending date E1.

=DAYS(E1, A1) will return the number of days between the two dates.

The purpose of using this function is to get the days between two dates.

The Return Value is a number representing the days.

SYNTAX -  

=DAYS(endDate, startDate)

ARGUMENTS - 

startDate(the starting date) and endDate(the ending date).

Both the start and end dates must be valid Excel dates. They can also be text values that can be parsed as dates. They should be whole numbers and not fractional values.

The Days function will return a negative value if we reverse the start and end dates.

Therefore, =DAYS(E1, A1) = E1-A1

EXAMPLES

  1. =DAYS("15-Jun-21", "1-Jun-21")
    //the function will return 14.
  2. If we want to include both the starting and ending date in the count, increase the count by 1.
    =DAYS("2-Jun-21", "1-Jun-21")+1
    //this will return 2

Points to Note

  1. We should avoid storing or parsing date represented by text values because it might produce errors. Thus it is better to work with native Excel dates.
  2. The Excel Days function works only with whole numbers.
  3. If the Days function can't recognize any date, it will return #VALUE! error.
  4. If the dates are out of range, it will return #NUM! error.


Excel Datedif Function

The Excel Datedif function returns the count of the number of days, months, or years between two given dates.

The purpose of this function is to the number of days, months, or years between two dates.

The Return Value is a number representing the time between two dates.

SYNTAX - 

=DATEDIF(startDate, endDate, unit)

ARGUMENTS - 

startDate(the starting date), endDate(the ending date), and the unit is the time unit that we want to use, and it is supplied as text.

DATEDIF = DATE+IF.

It is a compatibility function and comes from Lotus 1-2-3. 

Time Units

Unit Result
"y" Difference in complete years
"m" Difference in complete months
"d" Difference in days
"md" Difference in days, ignoring months and years
"ym" Difference in months, ignoring years
"yd" Difference in days, ignoring years

Examples

  1. =DATEDIF("1-Jan-18", "1-Mar-20", "y")
    //Returns 2
  2. =DATEDIF("1-Jan-18", "1-Mar-20", "m")
    //Returns 26
  3. =DATEDIF("1-Jan-18", "1-Mar-20", "d")
    //Returns 790
  4. =DATEDIF("1-Jun-15", "15-Sep-21", "my")
    //Returns 3 as it is completely ignoring the years and just counting the months.
  5. =DATEDIF("1-Jun-15", "15-Sep-21", "md")
    //Returns 14 as it is completely ignoring the months and years.
  6. =DATEDIF(A1, TODAY(), “y”)
    //This will return the current age of a person in years.

Points to Note

  1. Excel doesn't help us to find the DATEDIF function.
  2. If the startDate is greater than the endDate, the function throws a #NUM error.
  3. Microsoft recommends us not to use the "md" value for the time unit as it may give a negative result or a zero, or an inaccurate result.

FAQs

  1. What is the return value of the DAYS function?
    Number
  2. What type of number does the DAYS function work with?
    Whole Numbers.
  3. What should we avoid while using the DAYS function?
    We should prevent parsing the dates represented by text values as it may produce errors.
  4. What is the return value of the DATEDIF function?
    Number
  5. What happens if startDate is greater than endDate while using the Datedif Function?
    It will throw a #NUM error.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Key Takeaways

In this article, we have extensively discussed Excel's DAYS and DATEDIF functions and how and where we should use them.

We hope that this blog has helped you enhance your knowledge regarding the two functions of Excel. If you would like to learn more, check out our articles on How to create a Calendar in ExcelVLOOKUP FunctionToday and Now FunctionsDay, Month, and Year Function.
Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. 

Enrol in our courses and refer to the mock test and problems available.

Take a look at the interview experiences and interview bundle for placement preparations.
 

Do upvote our blog to help other ninjas grow. 

Happy Coding!

Previous article
Days, Month and Year Function
Next article
Index and Match Function
Live masterclass