calculations in excel
 

I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:
I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

Roger i tried that but it kept throwing up errors. It may be im just stupid
lol. I have the spreadsheet concerned if thats any help to you explaining it
to me

Mark

"Roger Govier" wrote:

Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:
I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

Hi Mark

I'm sorry. My stupid fault. Today() will always be greater than the dates
held in your cells, hence it should come second in the list
try
=DATEDIF(E2,TODAY(),"y")

Regards

Roger Govier


Mark C wrote:
Roger i tried that but it kept throwing up errors. It may be im just stupid
lol. I have the spreadsheet concerned if thats any help to you explaining it
to me

Mark

"Roger Govier" wrote:


Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:

I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

Roger

That worked a treat, im so shuffed. My wife will be overjoyed as she has the
task of keeping the membership up to date.
Some of the kids are 105 though lol, they havent given a dob.

Thanks Again

Mark :)

"Roger Govier" wrote:

Hi Mark

I'm sorry. My stupid fault. Today() will always be greater than the dates
held in your cells, hence it should come second in the list
try
=DATEDIF(E2,TODAY(),"y")

Regards

Roger Govier


Mark C wrote:
Roger i tried that but it kept throwing up errors. It may be im just stupid
lol. I have the spreadsheet concerned if thats any help to you explaining it
to me

Mark

"Roger Govier" wrote:


Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:

I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

Hi Mark

I fell like 105 myself, today!!!
The 105 comes from blank cells, which are treated as 01/01/1900, hence 105
years ago.
We can trap for that quite easily though with the following
=IF(E2="","",DATEDIF(E2,TODAY(),"y"))

Regards

Roger Govier


Mark C wrote:
Roger

That worked a treat, im so shuffed. My wife will be overjoyed as she has the
task of keeping the membership up to date.
Some of the kids are 105 though lol, they havent given a dob.

Thanks Again

Mark :)

"Roger Govier" wrote:


Hi Mark

I'm sorry. My stupid fault. Today() will always be greater than the dates
held in your cells, hence it should come second in the list
try
=DATEDIF(E2,TODAY(),"y")

Regards

Roger Govier


Mark C wrote:

Roger i tried that but it kept throwing up errors. It may be im just stupid
lol. I have the spreadsheet concerned if thats any help to you explaining it
to me

Mark

"Roger Govier" wrote:



Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:


I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark

calculations in excel
 

I think i'll cut my losses at what we have lol. It helps to flag up that one
or more of the little darlings havent told us anything lol.

Thanks again for your help Roger.

Mark

"Roger Govier" wrote:

Hi Mark

I fell like 105 myself, today!!!
The 105 comes from blank cells, which are treated as 01/01/1900, hence 105
years ago.
We can trap for that quite easily though with the following
=IF(E2="","",DATEDIF(E2,TODAY(),"y"))

Regards

Roger Govier


Mark C wrote:
Roger

That worked a treat, im so shuffed. My wife will be overjoyed as she has the
task of keeping the membership up to date.
Some of the kids are 105 though lol, they havent given a dob.

Thanks Again

Mark :)

"Roger Govier" wrote:


Hi Mark

I'm sorry. My stupid fault. Today() will always be greater than the dates
held in your cells, hence it should come second in the list
try
=DATEDIF(E2,TODAY(),"y")

Regards

Roger Govier


Mark C wrote:

Roger i tried that but it kept throwing up errors. It may be im just stupid
lol. I have the spreadsheet concerned if thats any help to you explaining it
to me

Mark

"Roger Govier" wrote:



Hi Mark

In another column (F?) on the sheet enter Age as a heading and in cell 2 enter
=DATEDIF(TODAY(),E2,"y")
assuming your date are in column E and starting in row 2, otherwise amend to
suit.
Copy this formula down for the 450 rows of your data.

Now, mark A1:F1, DataFilterAutofilter and use the dropdown on the Age
column to select 18

Regards

Roger Govier


Mark C wrote:


I have a spreadsheet that records members of an 13 to 18 year old disco club.
We record their Name, Address, Post Code, Phone Number and Date of Birth.
There are over 450 members.
What i am trying to do i get excel to alert me as to when a member reaches
18 years of age so i can remove them from the sheet.
Can someone assist me in creating some thing that can do this (If its
possible in excel)
Otherwise it takes an age trawling through the records. : (
Thanks

Mark