Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
I am responsible for peoples training therefore I am required to monitor when
refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Hi,
Try this in f3 =IF(DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))<TODAY(),"Re fresher Due","") You could also add a conditional form to F3 to turn it red for additional highlight. Select F3 Format|Conditional format Select cell value is enter the formula ="Refresher Due" Pick a colour and click OK Mike "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
hi
you can also use conditional formatintg. on the menu bar... Formatconditional format... formaula is...=IF(DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))TODAY( ),1,0)... pick your cell color. then in 1 year from the data of last refresher, the cell will turn color alerting you that a refresher is due. Regards FSt1 "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Mike H's solution is the way most would solve the problem - similar to the
way I've handled pretty much the same type of thing several times. But I might recommend one change if you need to keep track of the initial training date along with when refresher training was performed: have 2 date columns; the 1st holds the date that training was initially given, the second would hold the date that the training was LAST provided (at first both dates would be the same), then use the 2nd date for your comparison as to whether or not refresher training is needed. When they get the refresher training, you put that date in the 2nd date column. This lets you keep up with initial training date and date it was last refreshed. You may not need this, but for some of the work I've done, it was necessary to track both dates. "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Thanks for that JLatham it sounds like you have just the thing I looking at.
However I am a little confused I wouder if you could send me an example. It sounds like this could be used year after year -- pk "JLatham" wrote: Mike H's solution is the way most would solve the problem - similar to the way I've handled pretty much the same type of thing several times. But I might recommend one change if you need to keep track of the initial training date along with when refresher training was performed: have 2 date columns; the 1st holds the date that training was initially given, the second would hold the date that the training was LAST provided (at first both dates would be the same), then use the 2nd date for your comparison as to whether or not refresher training is needed. When they get the refresher training, you put that date in the 2nd date column. This lets you keep up with initial training date and date it was last refreshed. You may not need this, but for some of the work I've done, it was necessary to track both dates. "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Thank you for your help, much appreciated
-- pk "FSt1" wrote: hi you can also use conditional formatintg. on the menu bar... Formatconditional format... formaula is...=IF(DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))TODAY( ),1,0)... pick your cell color. then in 1 year from the data of last refresher, the cell will turn color alerting you that a refresher is due. Regards FSt1 "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Very Useful, thank you
-- pk "Mike H" wrote: Hi, Try this in f3 =IF(DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))<TODAY(),"Re fresher Due","") You could also add a conditional form to F3 to turn it red for additional highlight. Select F3 Format|Conditional format Select cell value is enter the formula ="Refresher Due" Pick a colour and click OK Mike "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
But in that case you don't need the IF(...,1,0)
You are looking for a TRUE/ FALSE Boolean condition, so all you need is: =DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))TODAY() -- David Biddulph "FSt1" wrote in message ... hi you can also use conditional formatintg. on the menu bar... Formatconditional format... formaula is...=IF(DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))TODAY( ),1,0)... pick your cell color. then in 1 year from the data of last refresher, the cell will turn color alerting you that a refresher is due. Regards FSt1 "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatic alerts in excell
Can you tell me, If I wanted the due date to show rather then the words
REFRESHER DUE how would I do that. -- pk "JLatham" wrote: Mike H's solution is the way most would solve the problem - similar to the way I've handled pretty much the same type of thing several times. But I might recommend one change if you need to keep track of the initial training date along with when refresher training was performed: have 2 date columns; the 1st holds the date that training was initially given, the second would hold the date that the training was LAST provided (at first both dates would be the same), then use the 2nd date for your comparison as to whether or not refresher training is needed. When they get the refresher training, you put that date in the 2nd date column. This lets you keep up with initial training date and date it was last refreshed. You may not need this, but for some of the work I've done, it was necessary to track both dates. "PK" wrote: I am responsible for peoples training therefore I am required to monitor when refreshment courses are due. For this I would like to create an excel dtabase in to which I can place a date in one cell and then when a set periods has passed I am informed that a refresher is due. for example. E3 may contain the date for which an individual first attended a course i.e. 21st Oct 2007.I the would then like cell F3 to show 'Refresher Due' once 12 months had passed. In the mean time I would like cell F3 to be blanc. I realise that I may have to have a Cell, A1 for example with 'todays date' in to give F3 a reference.can this be done? Thank you for your help. -- pk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email alerts | Excel Discussion (Misc queries) | |||
alerts | Excel Worksheet Functions | |||
MsgBox alerts | Excel Worksheet Functions | |||
Alerts? | New Users to Excel | |||
Automatic page breaks in Excell 2003 | Excel Discussion (Misc queries) |