Date Reminders

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