ASSIGNMENT – 35

USE OF DATEDIF FORMULA

Calculate Age & Difference Between Two Dates

Objective

In this assignment, you will learn how to use the DATEDIF formula to:

  • Calculate the difference between two dates

  • Calculate total days, months and years

  • Calculate remaining months and days

  • Calculate the current age of students

  • Create a complete age statement using Excel formulas

PART A – DATE DIFFERENCE

Table 1 – Date Difference

Enter the following data in Excel:

First Date Second Date Interval Difference
15-01-2010 20-06-2020 Days  
15-01-2010 20-06-2020 Months  
15-01-2010 20-06-2020 Years  
15-01-2010 20-06-2020 Year-Days  
15-01-2010 20-06-2020 Year-Months  
15-01-2010 20-06-2020 Month-Days  

DATEDIF Syntax

=DATEDIF(Start_Date,End_Date,Unit)

Important DATEDIF Units

Unit Meaning
"d" Total number of Days
"m" Total number of complete Months
"y" Total number of complete Years
"yd" Remaining Days after completed Years
"ym" Remaining Months after completed Years
"md" Remaining Days after completed Months

Questions – Part A

Q1. Calculate the total number of Days between the First Date and Second Date.

Hint:

=DATEDIF(A2,B2,"d")

Q2. Calculate the total number of complete Months between the two dates.

Hint:

=DATEDIF(A2,B2,"m")

Q3. Calculate the total number of complete Years between the two dates.

Hint:

=DATEDIF(A2,B2,"y")

Q4. Calculate the remaining Days after completed Years using "yd".

Hint:

=DATEDIF(A2,B2,"yd")

Q5. Calculate the remaining Months after completed Years using "ym".

Hint:

=DATEDIF(A2,B2,"ym")

Q6. Calculate the remaining Days after completed Months using "md".

Hint:

=DATEDIF(A2,B2,"md")

PART B – STUDENT DATE OF BIRTH

Table 2 – Student Age Calculation

Enter the following data in Excel:

Name Date of Birth Years Months Days
Aarav 12-03-2005      
Riya 25-07-2008      
Karan 18-11-2003      
Neha 05-02-2010      
Rahul 21-09-2006      
Simran 14-12-2004      

Important

Use TODAY() as the second date so that the age automatically updates every day.

For example:

Years

=DATEDIF(B2,TODAY(),"y")

Remaining Months

=DATEDIF(B2,TODAY(),"ym")

Remaining Days

=DATEDIF(B2,TODAY(),"md")

Questions – Part B

Q7. Calculate the current Age in completed Years of all students using:

DATEDIF

and

TODAY()

Q8. Calculate the completed Months after the completed Years for all students.

Hint:

=DATEDIF(B2,TODAY(),"ym")

Q9. Calculate the remaining Days after the completed Months for all students.

Hint:

=DATEDIF(B2,TODAY(),"md")

Q10. Create a Complete Age Statement

Create a new column named Age Statement.

The statement should appear in this format:

Aarav is 21 Years, 5 Months and 10 Days old.

Use the following Excel functions:

  • DATEDIF

  • TODAY()

  • & (Text Concatenation)

Formula Pattern

=A2&" is "&DATEDIF(B2,TODAY(),"y")&" Years, "&DATEDIF(B2,TODAY(),"ym")&" Months and "&DATEDIF(B2,TODAY(),"md")&" Days old."

Copy the formula down for all students.

BONUS PRACTICE

Try creating these additional statements:

1. Total Months

=DATEDIF(B2,TODAY(),"m")

2. Total Days

=DATEDIF(B2,TODAY(),"d")

3. Age Statement with TODAY()

Create a statement such as:

“Riya is currently 18 Years, 2 Months and 15 Days old.”

Learning Outcome

After completing this assignment, students will be able to:

  1. Use the DATEDIF function.

  2. Calculate the difference between two dates.

  3. Calculate total Days, Months and Years.

  4. Use Y, M, D, YD, YM and MD.

  5. Use TODAY() for automatic age calculation.

  6. Calculate a person’s exact age in Years, Months and Days.

  7. Combine formulas with text using &.

  8. Create dynamic age statements that update automatically.

PreviousNext