Date Reminders

[Guest]

Hi
I have a list of exam dates and I need to do refresher courses every so often.
I would like excel to remind me 1 month before a course refresher is due and
also if I am out of date for any courses.
What is the best way to go about this in excel?
Thanks

 

[Bob Phillips]

Use conditional formatting. Look it up in help, and the post back with rules
that you want to embed for further help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks for that. At present i use:
=DATEDIF(D4,$BB$3,"d")>365
Then I use the true/false result with conditional formatting to give a green
box if in date, a red box if out of date. Cell BB3 uses the =TODAY() function
to get the current date.
What I would also like is another value 1 month before I am out of date
which I could then format yellow.

 

[Bob Phillips]

Mariner,

Firstly, I wouldn't store TODAY() in a cell, I would use

=DATEDIF(D4,TODAY(),"d")>365

With DATEDIF you cannot have a month early, but you could just do 30 days
early, like

=DATEDIF(D4,TODAY(),"d")>335

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob

That’s a simpler formula. As you maybe can tell I am new to excel.
Instead of the formula returning true or false would it be possible for the
formula to return for instance 1, 2 or 3.
So if the result is less than 335 it returns 1.
If less than 365 but more than 335 it returns 2.
If more than 365 it returns 3.

Thanks

Mariner

 

[Bob Phillips]

=IF(DATEDIF(D4,TODAY(),"d")>365,3,IF(DATEDIF(D4,TODAY(),"d")>335,2,1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob

Works Great

=IF(DATEDIF(D4,TODAY(),"d")>365,3,IF(DATEDIF(D4,TODAY(),"d")>335,2,1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)