View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Reg
 
Posts: n/a
Default Conditional formatting based on a date + 30 days

in the conditional format c2 use 'formula is' and put

=AND(ISBLANK($B19),($C19+30TODAY()))

Then use format painter to replicate it down the column

Reg


"Laura4363" wrote:

I've been looking at this all day and I'm sure there's a very simple answer,
maybe a fresh pair of eyes might be able to help me out!
I have a spreadsheet (in Excel 2003) which looks roughly like this:

A B C
1 Sent Returned Overdue
2 10/12/05 04/01/06
3 20/12/05
4 12/10/05

What I want is, if the item hasn't been returned within 30 days of the sent
date, the appropriate cell in the overdue column should turn red. But if it
has been returned, or it's not due yet, the cell should stay blank (i.e.
white). So in the example above, cells C2 and C3 would be blank, and C4
would be red.

I've tried conditional formatting, but I can't figure out how to get it to
work out the "date + 30 days" bit in column A. Plus I need it to ignore
column A if there's something in column B. I've found various answers but
they all seem to relate to comparing everything to today's date and that's
not what I want!

Any help would be appreciated, and I'm sorry if it's a bit confusing!