ASSIGNMENT – 7 – ANSWER KEY

Calculate Date of Birth & Age

Completed Data Table

Age calculated as on 06-10-2026

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

Q.1 COUNTA – Total Students

Assuming names are in B2:

=COUNTA(B2:B13)

Answer: 12 Students

Q.2 DAY Formula

In D2:

=DAY(C2)

Copy down to D13.

Answers

12, 25, 18, 10, 5, 22, 15, 30, 8, 20, 14, 6

Q.3 MONTH Formula

In E2:

=MONTH(C2)

Copy down to E13.

Answers

4, 9, 2, 11, 7, 3, 8, 1, 6, 12, 10, 5

The MONTH function returns the month number from 1 to 12.

Q.4 YEAR Formula

In F2:

=YEAR(C2)

Copy down to F13.

Answers

1985, 1998, 2004, 1992, 2001, 1988, 2008, 2005, 1995, 2010, 1982, 2003

Q.5 DATEDIF – Calculate Age

In G2:

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

Copy down to G13.

Age Answers

Name Age
ANKIT 41
PRIYA 28
ROHAN 22
NEHA 33
VIKAS 25
POOJA 38
AMAN 18
KIRAN 21
RAVI 31
SIMRAN 15
ARJUN 43
DIVYA 23

Q.6 COUNTIF – Age Greater Than 20

Formula:

=COUNTIF(G2:G13,">20")

Students older than 20:

  • ANKIT – 41

  • PRIYA – 28

  • ROHAN – 22

  • NEHA – 33

  • VIKAS – 25

  • POOJA – 38

  • KIRAN – 21

  • RAVI – 31

  • ARJUN – 43

  • DIVYA – 23

Answer: 10 Students

Q.7 COUNTIF – Age 25 or Older

Formula:

=COUNTIF(G2:G13,">=25")

Answer: 7 Students

They are:

ANKIT, PRIYA, NEHA, VIKAS, POOJA, RAVI and ARJUN

Q.8 IF – Adult or Child

In H2:

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

Copy down to H13.

Results

Name Age Result
ANKIT 41 Adult
PRIYA 28 Adult
ROHAN 22 Adult
NEHA 33 Adult
VIKAS 25 Adult
POOJA 38 Adult
AMAN 18 Child
KIRAN 21 Adult
RAVI 31 Adult
SIMRAN 15 Child
ARJUN 43 Adult
DIVYA 23 Adult

Final Count

Adult = 10
Child = 2

Q.9 SUMIF – Total Age of Students Greater Than 20

Formula:

=SUMIF(G2:G13,">20",G2:G13)

Calculation:

41 + 28 + 22 + 33 + 25 + 38 + 21 + 31 + 43 + 23

Answer: 305 Years

Q.10 COUNTIF – Born in 2000 or Later

Using the YEAR column (F2):

=COUNTIF(F2:F13,">=2000")

Students born in 2000 or later:

  • ROHAN – 2004

  • VIKAS – 2001

  • AMAN – 2008

  • KIRAN – 2005

  • SIMRAN – 2010

  • DIVYA – 2003

Answer: 6 Students

Final Answers

Question Answer
Q.1 Total Students 12
Q.6 Age > 20 10
Q.7 Age ≥ 25 7
Q.8 Adult 10
Q.8 Child 2
Q.9 Total Age of Age > 20 305
Q.10 Born in 2000 or Later 6
PreviousNext