Calculating someone's age at a given date

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

I work at a school and I need to create a spreadsheet which tells me when a
child reaches the age of 5 on any given date. Can anyone help. Thanks. Deb
 
so, you have their DOB and another date and want to know if the given date is
greater than or equal to 5 years from DOB...

Assume DOB is in cell A2 and given date is in B2. In calculated field:

=IF(B2>=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2)),"5 or older","Under 5")
 
With date of birth in A1

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))

If this post helps click Yes
 
Thanks David. Could I trouble you for another one? Is there any calculation
that will tell me WHEN a child reaches the age of 5. For example I have a
date of birth of 1/5/03 and I want to know the date when they reach the age
of 5. I know this is easily calculated in your head but I work in a school
and a simple spreadsheet and a click of a button would help me greatly! From
a very inexperiened excel user. Deb
 
Well, I think you'd want to go with a version of mine..

=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
 
Just use the relevant part of Sean's formula:
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
 
If you wanted to find out of each child when they will reach the age of 5 then
this is the formula so all the DOB should be in column A and starting from
Row 5 and the absolute refernce which in this case is age 5 should be just
typed in column B row 4 and this formula is working.

=DATE(YEAR(A)+$B$4,MONTH(A5),DAY(A5))
 
Hi Tanya - sorry i could not get this to work. I really am a novice so I am
prob doing something wrong. From your description i put a list of dates of
birth starting in A5 and then in B4 typed in your formula. I also do not
understand what you mean by absolute reference. Thanks
 
In the below, cell B4 would be where you enter the desired age. So, for you,
B4 would have 5.

Then, the formula would go next to the child's DOB.

Abslute reference means that no matter where you put the below formula in
the sheet, it will always look at cell B4 for your entered age.

The $ before the B means always look at column B. The $ before the 4 means
always look at row 4. Having a $ before both means always look at B4.

Without the first $, if you copied the formula across a bunch of columns,
the B would become a C, then a D, etc.

same for the 2nd for rows.

Hope this helps!
 
Back
Top