ASSIGNMENT – 7

Calculate Date of Birth & Age

Use of Formulas – COUNTA, DAY, MONTH, YEAR, COUNTIF, SUMIF, IF & DATEDIF

Student Data

Sr. No. Name Date of Birth Day Month Year Age
1 ANKIT 12-04-1985        
2 PRIYA 25-09-1998        
3 ROHAN 18-02-2004        
4 NEHA 10-11-1992        
5 VIKAS 05-07-2001        
6 POOJA 22-03-1988        
7 AMAN 15-08-2008        
8 KIRAN 30-01-2005        
9 RAVI 08-06-1995        
10 SIMRAN 20-12-2010        
11 ARJUN 14-10-1982        
12 DIVYA 06-05-2003        

Questions

Q.1 COUNTA Formula

How many students are there in the list?

Use the COUNTA formula.

Q.2 DAY Formula

Calculate the Day of Birth for all students using the DAY formula.

Q.3 MONTH Formula

Calculate the Month of Birth for all students using the MONTH formula.

Q.4 YEAR Formula

Calculate the Year of Birth for all students using the YEAR formula.

Q.5 DATEDIF Formula – Calculate Age

Calculate the present age of each student using the DATEDIF formula.

Use the current date as the end date.

Example:

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

Q.6 COUNTIF – Age Greater Than 20

Using COUNTIF, calculate how many students are greater than 20 years old (>20).

Q.7 COUNTIF – Age 25 or Older

Using COUNTIF, calculate how many students are 25 years or older (>=25).

Q.8 IF Formula – Adult or Child

Using the IF formula, display:

  • “Adult” if the student’s age is greater than 20

  • “Child” otherwise

Example:

=IF(G2>20,"Adult","Child")

Q.9 SUMIF – Total Age

Using SUMIF, calculate the total age of all students who are greater than 20 years old.

Q.10 COUNTIF – Birth Year

Using COUNTIF, calculate how many students were born in 2000 or later (>=2000).

Formulas to Practice

COUNTA | DAY | MONTH | YEAR | DATEDIF | COUNTIF | SUMIF | IF

Important Formulas

DAY:

=DAY(C2)

MONTH:

=MONTH(C2)

YEAR:

=YEAR(C2)

AGE:

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

ADULT / CHILD:

=IF(G2>20,"Adult","Child")
PreviousNext