help with datdiff and conditional format

  • Thread starter Thread starter OdAwG
  • Start date Start date
O

OdAwG

Hello All Excel Guru's,

I was wondering if anyone can help me out with a formula using datediff.
what I am trying to do is compare two dates (todays date against a column
with certain dates in it). If the dates falls less than 7 days, then change
the font or background color Red. if the days fall within 14 days, change
the font or background color to blue.

Example:

A B
07/20/2006 07/30/2006


formula so far.

=datedif(today(), a1,"d") would give me 3 days for A using the same
formula for column b would give me 13 for B

now I modify the formula alittle to the following, and this is where I need
help:

=if(datedif(today(),a1,"d")<7,"Change Color to Red", leave color alone)
=if(datedif(today(),b1,"d")<14,"Change Color to Blue", leave color alone)

I was looking into Conditional format, but my formula above is not complete.
I was wondering if I should jsut create another column and store the results
in that and then use conditional format so that my formula would be if
column C is <7 change the background to red. And do another one with <14
for blue.

Would that be the best way to handle this...
 
I would think the best way is to use CF. Select your cells with A1
active. Set the dropdowns and input boxes to read:

CF1: Formula is =(A1-TODAY())<7
Format1: <patterns>/<red>

There's no need to use DateDif, simple subtraction of dates works fine
(you may need to format the result as General or Number if you have the
formula in a cell).
 
Back
Top