Date Reminders

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)

"Mariner" <Mariner@discussions.microsoft.com> wrote in message
news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> 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

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" wrote:

> 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)
>
> "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> > 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
>
>
>

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)

"Mariner" <Mariner@discussions.microsoft.com> wrote in message
news:412FD90E-0B52-438C-9407-3E1DD2FDFED7@microsoft.com...
> 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" wrote:
>
> > 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)
> >
> > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> > > 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
> >
> >
> >

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" wrote:

> 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)
>
> "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> news:412FD90E-0B52-438C-9407-3E1DD2FDFED7@microsoft.com...
> > 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" wrote:
> >
> > > 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)
> > >
> > > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > > news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>

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

"Mariner" <Mariner@discussions.microsoft.com> wrote in message
news:3B0C4916-3960-4B02-8ECF-BFEBC3B67E6D@microsoft.com...
> 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" wrote:
>
> > 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)
> >
> > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > news:412FD90E-0B52-438C-9407-3E1DD2FDFED7@microsoft.com...
> > > 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" wrote:
> > >
> > > > 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)
> > > >
> > > > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > > > news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

Thanks Bob

Works Great

"Bob Phillips" wrote:

> =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)
>
> "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> news:3B0C4916-3960-4B02-8ECF-BFEBC3B67E6D@microsoft.com...
> > 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" wrote:
> >
> > > 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)
> > >
> > > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > > news:412FD90E-0B52-438C-9407-3E1DD2FDFED7@microsoft.com...
> > > > 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" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > > "Mariner" <Mariner@discussions.microsoft.com> wrote in message
> > > > > news:F7902120-754B-4AEA-AB37-7513A05CC5DA@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>