| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I interupt excel 2003 calculations? | Excel Discussion (Misc queries) | |||
| Microsoft Excel in Microsoft works - how to open | Excel Discussion (Misc queries) | |||
| Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
| time interval calculations in excel | Excel Discussion (Misc queries) | |||
| Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
Linear Mode
