📊 Excel Basics #30 – DATEDIF() & EDATE() Functions

When working with employee records, project timelines, subscriptions, loans, or customer data, you often need to calculate the time between dates or move a date forward or backward by a specific number of months.

Two useful functions are DATEDIF() and EDATE().

📌 1. DATEDIF() Function

DATEDIF() calculates the difference between two dates.

Syntax:

=DATEDIF(start_date,end_date,unit)

The unit determines what you want to calculate.

Common units:

• Y → Complete years

• M → Complete months

• D → Total days

• YM → Remaining months after complete years

• YD → Remaining days after complete years

• MD → Remaining days after complete months

📌 Example 1 – Calculate Complete Years

Suppose:

A2 = 01-Jan-2020

B2 = 19-Aug-2026

Formula:

=DATEDIF(A2,B2,"Y")

Result:

6

The employee has completed 6 full years.

📌 Example 2 – Calculate Complete Months

=DATEDIF(A2,B2,"M")

This returns the total number of complete months between the two dates.

📌 Example 3 – Calculate Total Days

=DATEDIF(A2,B2,"D")

This returns the total number of complete days between the dates.

📌 Example 4 – Display Years and Months

You can combine multiple DATEDIF() functions:

=DATEDIF(A2,B2,"Y")&" Years "&DATEDIF(A2,B2,"YM")&" Months"

Example result:

6 Years 7 Months

This is useful for calculating employee tenure or customer relationship duration.

📌 2. EDATE() Function

EDATE() returns a date that is a specified number of months before or after a starting date.

Syntax:

=EDATE(start_date,months)

📌 Example 1 – Add Months

If:

A2 = 19-Aug-2026

Formula:

=EDATE(A2,3)

Result:

19-Nov-2026

📌 Example 2 – Subtract Months

=EDATE(A2,-3)

This returns the date 3 months before the date in A2.

Result:

19-May-2026

📌 Real-World Example

Suppose a subscription starts on:

19-Aug-2026

and lasts for 12 months.

Formula:

=EDATE(A2,12)

Result:

19-Aug-2027

You can use this to calculate renewal dates.

📌 DATEDIF() vs EDATE()

DATEDIF() → Calculates the difference between dates.

EDATE() → Calculates a new date by adding/subtracting months.

Think:

👉 DATEDIF() → How long?

👉 EDATE() → What date after/before X months?

📌 Real-World Uses

• Calculate employee experience.

• Calculate customer tenure.

• Calculate project duration.

• Find subscription renewal dates.

• Calculate loan or contract dates.

• Track service anniversaries.

📌 Common Mistakes

❌ Putting the end date before the start date in DATEDIF().

❌ Using the wrong DATEDIF() unit.

❌ Forgetting that DATEDIF() returns complete units, not rounded values.

❌ Formatting an EDATE() result as a number instead of a date.

✅ Quick Tip

Remember:

DATEDIF() → Difference between two dates

EDATE() → Move a date by months

These functions are extremely useful when working with real-world business data.

Double Tap ❤️ For More