What do we know about DATEDIF Function?
Have you ever needed to figure out someone’s age, check how many EMIs are still left on your home loan, or count the days until your next LIC premium is due? There’s an Excel hidden function called DATEDIF that can do all of this effortlessly!
But here’s something interesting Microsoft never officially lists this function in its documentation. That means if you try typing DATEDIF function in Excel, it won’t show up in the suggestions like other functions do.
So, why does this happen? Well, this function actually comes from an older spreadsheet software called Lotus 1-2-3, which was popular in the 90s before Excel took over. Even though Excel still supports DATEDIF, it doesn’t actively promote it.
DATEDIF is a powerful tool once you know how to use it. Whether you’re tracking payments, managing deadlines, or calculating time differences, this function can save you a lot of effort.
How Does DATEDIF Work?
DATEDIF calculates the difference between two dates in:
✅ Years (e.g., someone’s age)
✅ Months (e.g., months left on a loan)
✅ Days (e.g., days till your flight)
Syntax:
=DATEDIF(start_date, end_date, unit)
📌 start_date – The earlier date (e.g., Date of Birth, Loan Start Date).
📌 end_date – The later date (e.g., Today’s Date, Loan End Date).
📌 unit – Defines what you want (years, months, days, etc.).
Available Units & What They Mean
| Unit | What It Returns | Example Use Case |
| “y” | Complete years between dates | Calculate age in excel from birth date |
| “m” | Complete months between dates | Find months left on a loan |
| “d” | Total days between dates | Count days till an event |
| “md” | Days, ignoring months & years | Get exact days left after removing months and years |
| “ym” | Months, ignoring years | Find remaining months after full years |
| “yd” | Days, ignoring years | Calculate days between dates in different years |
Real-World Examples in an Indian Context 🇮🇳
Example 1: Calculate Age from Birth Date
You want to calculate the age of a person born on 15th August 1995.
=DATEDIF(DATE(1995,8,15), TODAY(), “y”)
Returns: 28 (if today is 2024)
Pro Tip: Use this to check if someone is eligible for a bank loan (most banks in India require a minimum age of 21 years).
Example 2: How Many EMIs Are Left on a Loan?
You took a car loan on 1st April 2022, and the loan ends on 1st April 2027. How many months are left?
=DATEDIF(DATE(2022,4,1), DATE(2027,4,1), “m”)
Returns: 60 months
Pro Tip: You can use this function to calculate months left for your SIP maturity, insurance policy renewal, or loan payoff.
Example 3: Days Left Until Your Flight
Your flight to Goa for vacation is on 25th December 2024. How many days are left?
=DATEDIF(TODAY(), DATE(2024,12,25), “d”)
Returns: Number of days left
Use this for tracking upcoming LIC premium payments, credit card due dates, or tax deadlines.
Example 4: How Many Full Years, Months, and Days Left for Home Loan?
Your home loan started on 1st January 2015, and you’re paying it off till 1st January 2035.
How many years, months, and days are left?
=DATEDIF(DATE(2015,1,1), DATE(2035,1,1), “y”) & ” years, ” & DATEDIF(DATE(2015,1,1), DATE(2035,1,1), “ym”) & ” months, ” & DATEDIF(DATE(2015,1,1), DATE(2035,1,1), “md”) & ” days”
Returns: 10 years, 5 months, 17 days (example)
Useful for loan planning, long-term investments, and mutual fund maturity calculations.
Example 5: Calculate Experience in a Job
You joined a company on 10th June 2016 and want to know your total experience.
=DATEDIF(DATE(2016,6,10), TODAY(), “y”)
Returns: 8 years (if today is 2024)
For exact months and days too:
=DATEDIF(DATE(2016,6,10), TODAY(), “y”) & ” years, ” & DATEDIF(DATE(2016,6,10), TODAY(), “ym”) & ” months, ” & DATEDIF(DATE(2016,6,10), TODAY(), “md”) & ” days”
Returns: 8 years, 2 months, 5 days (if today is 15th August 2024)
Use this to calculate work experience for job applications or promotions.
Bonus: Alternative Way to Calculate Days Between Dates
You can simply subtract dates in Excel to get the number of days:
= B1 – A1
Works when B1 = End Date and A1 = Start Date
Things to Watch Out For
❌ Error If Start Date > End Date → You’ll get #NUM! error if the end date is earlier than the start date.
❌ The “MD” Unit Can Be Inconsistent → Microsoft warns that “md” may give unreliable results.
❌ Excel Won’t Auto-Suggest DATEDIF → You need to type it manually.
For More Information You Can Visit:
- Mastering Date & Time Functions in Excel: A Complete Guide (Udemy)
- Loan EMI calculation and Maturity Dates in Excel (ICICI Bank)
- The Top 10 Hidden Excel Functions You Should Know (YouTube)
- How to Automate Excel Date Calculations Without Mistakes (Microsoft Support)
- 50+ Powerful Excel Shortcuts to Skyrocket Your Productivity (Grad Me Up!)
Final Thoughts
DATEDIF is hidden gem for Excel date calculations. From tracking loan payments to calculating work experience, days left for travel, or even your SIP maturity, this function can make life easier.While Excel won’t show it in auto-suggestions, it still works perfectly when used right. So, next time you need a quick date difference, give DATEDIF a try!
FAQ’s Related to DATEDIF Function
Why is the DATEDIF function not shown in Excel’s suggestions?
The DATEDIF function comes from Lotus 1-2-3, an old spreadsheet program. While Excel still supports it, Microsoft doesn’t officially promote it.
Can I use DATEDIF to calculate the number of working days?
No, DATEDIF only counts total days. For working days, use the NETWORKDAYS function instead.
What does the “MD” unit do in DATEDIF, and why is it unreliable?
The “MD” unit calculates the remaining days after ignoring months and years, but Microsoft warns that it may give inconsistent results in some cases.
How do I avoid errors when using DATEDIF?
Ensure the start date is earlier than the end date to prevent a #NUM! error. Also, double-check your unit values to avoid unexpected results.
