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")

"Deb" wrote:

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

With date of birth in A1

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

If this post helps click Yes
---------------
Jacob Skaria


"Deb" wrote:

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

or =IF(DATEDIF(A2,B2,"y")=5,"5 or older","Under 5")
--
David Biddulph

"Sean Timmons" wrote in message
...
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")

"Deb" wrote:

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

That would be less work. Thank you for that one.

"David Biddulph" wrote:

or =IF(DATEDIF(A2,B2,"y")=5,"5 or older","Under 5")
--
David Biddulph

"Sean Timmons" wrote in message
...
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")

"Deb" wrote:

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

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

"David Biddulph" wrote:

or =IF(DATEDIF(A2,B2,"y")=5,"5 or older","Under 5")
--
David Biddulph

"Sean Timmons" wrote in message
...
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")

"Deb" wrote:

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

Well, I think you'd want to go with a version of mine..

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

"Deb" wrote:

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

"David Biddulph" wrote:

or =IF(DATEDIF(A2,B2,"y")=5,"5 or older","Under 5")
--
David Biddulph

"Sean Timmons" wrote in message
...
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")

"Deb" wrote:

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

Just use the relevant part of Sean's formula:
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
--
David Biddulph

"Deb" wrote in message
...
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

"David Biddulph" wrote:

or =IF(DATEDIF(A2,B2,"y")=5,"5 or older","Under 5")
--
David Biddulph

"Sean Timmons" wrote in message
...
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")

"Deb" wrote:

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

"Deb" wrote:

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

"Deb" wrote:

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

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))


"Deb" wrote:

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

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

"Tanya" wrote:

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))


"Deb" wrote:

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

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!

"Deb" wrote:

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

"Tanya" wrote:

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))


"Deb" wrote:

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