Highlighting a Past Date

[Guest]

I'm attempting to set up a spreadsheet that keeps track of pending cases
where a RECEIVED DATE is posted causing a DUE DATE to automatically propagate.

I have no problem setting up the pending formula (something like "A1 + 30")
but rather in the conditional formatting: I want the DUE DATE cell to format
to red when that date is past due (something like "IF < TODAY()"). The
problem is then in those cells that don't have a RECEIVED DATE yet (blank).
The DUE DATE column will automatically "go to red" since Excel reads a blank
cell as "zero" and computes that day as "thirty" which is less than today's
date.

Any ideas?

Thanks

 

[daddylonglegs]

For your due date formula use

=IF(A1="","",A1+30)

so that if your RECEIVED date is blank the DUE date will also be blank

Your conditional formatting should now work OK as "" is deemed b
greater than any number. If you have any actual blank in conditiona
formatting range try using this formula in conditional formatting

=ISNUMBER(B1)*(B1<TODAY()

 

[somethinglikeant]

Add a test to see if the cell to the left is empty or not by using the
AND statements to adda second condition.


=AND(B1<TODAY(),ISBLANK(A1)=FALSE)


somethinglikeant