help with datdiff and conditional format



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.


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

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

JE McGimpsey

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question