ASSIGNMENT – 41

USE OF DATEDIF – AGE CALCULATION

Objective

In this assignment, you will learn how to calculate a student’s exact age in Years, Months and Days using DATEDIF() and TODAY().

You will also use:

  • DATEDIF

  • TODAY

  • IF

  • COUNTIF

  • & (Concatenation Operator)

STUDENT DETAILS

S.No. Name Date of Birth Years Months Days Age Status Complete Age
1 Aarav 15-05-1998          
2 Meera 20-08-2001          
3 Rahul 10-10-2005          
4 Pooja 25-03-1995          
5 Karan 12-07-2008          
6 Simran 18-11-1999          
7 Mohit 05-02-2010          
8 Neha 22-09-2003          
9 Arjun 30-01-1990          
10 Priya 14-06-2006          

Important Instruction

Do not use a fixed year or fixed date for age calculation.

Always use:

TODAY()

with DATEDIF() so that the age automatically updates according to the current date.

QUESTIONS

Q1. Count the Number of Students

Find the total number of students in the list.

Function: COUNTA

=COUNTA(B2:B11)

Q2. Calculate Completed Years

Calculate the completed age in Years for every student.

Function: DATEDIF

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

Q3. Calculate Completed Months

Calculate the number of remaining complete months after completed years.

Function: DATEDIF

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

Q4. Calculate Remaining Days

Calculate the number of remaining days after completed months.

Function: DATEDIF

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

Q5. Create Complete Age

Create the complete age in one cell in the following format:

28 Years, 3 Months and 6 Days

Use:

DATEDIF + TODAY + &

Example formula:

=DATEDIF(C2,TODAY(),"y")&" Years, "&DATEDIF(C2,TODAY(),"ym")&" Months and "&DATEDIF(C2,TODAY(),"md")&" Days"

Q6. Calculate Age Status

If the student’s age is 18 years or above, display:

Adult

Otherwise display:

Minor

Functions: IF + DATEDIF

=IF(D2>=18,"Adult","Minor")

Q7. Count Students Aged 20 Years or Older

Find how many students are 20 years or older.

Function: COUNTIF

=COUNTIF(D2:D11,">=20")

Q8. Count Students Aged 25 Years or Older

Find how many students are 25 years or older.

Function: COUNTIF

=COUNTIF(D2:D11,">=25")

Q9. Find Pooja’s Exact Age

Find Pooja’s age in:

  • Completed Years

  • Remaining Months

  • Remaining Days

Function: DATEDIF

You may use:

=DATEDIF(C5,TODAY(),"y")
=DATEDIF(C5,TODAY(),"ym")
=DATEDIF(C5,TODAY(),"md")

Also create her complete age in one cell.

Q10. PRACTICAL CHALLENGE ⭐

Create a single Age Details column that automatically displays information in this format:

Aarav is 28 Years, 3 Months and 6 Days old – Adult

The result must automatically update according to the current date.

Use:

DATEDIF + TODAY + IF + &

Suggested Formula:

=B2&" is "&DATEDIF(C2,TODAY(),"y")&" Years, "&DATEDIF(C2,TODAY(),"ym")&" Months and "&DATEDIF(C2,TODAY(),"md")&" Days old – "&IF(D2>=18,"Adult","Minor")

EXTRA PRACTICE ⭐

After completing the assignment, try to answer:

  1. Who is the oldest student?

  2. Who is the youngest student?

  3. How many students are Minors?

  4. How many students are Adults?

  5. Which student has completed exactly 20 years or more but less than 25 years?

Functions to Explore

DATEDIF | TODAY | IF | COUNTIF | &

PreviousNext