automatic alerts in excell

[PK]

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.

 

[Mike H]

Hi,

Try this in f3
=IF(DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))<TODAY(),"Refresher 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

 

[FSt1]

hi
you can also use conditional formatintg.
on the menu bar...
Format>conditional 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

 

[JLatham]

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]

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]

Thank you for your help, much appreciated
--
pk

hi
you can also use conditional formatintg.
on the menu bar...
Format>conditional 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]

Very Useful, thank you
--
pk

Hi,

Try this in f3
=IF(DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))<TODAY(),"Refresher 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

 

[David Biddulph]

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

 

[PK]

Can you tell me, If I wanted the due date to show rather then the words
REFRESHER DUE how would I do that.