Conditional formatting with dates

Formula used in a conditional format must return
a boolean (TRUE/FALSE) result. Try using
something like...

=A1=TODAY()-1

where A1 is the cell containing the date that
will be formatted.

Mark

Thanks for the reply. I tried this but it doesn't appear to work. First I tried with the column range and then I tried using a single cell and using the format painter for the other cells.

Grant

Grant,

Mark's proposal should work.

Activate A1. On Conditional Setting dialog, do the following.

Choose "Formula is" for Condition 1 and enter:

=A1=TODAY()-1

Click on "Format", on "Patterns" tab choose Red.

Activate "Add"

Choose "Formula is" for Condition 2 and enter:

=A1 < TODAY()-1

Activate "Format", choose Blue on "Patterns" tab.

Close the dialog. While in A1, activate Format Painter, select the rest of the date cells in A and let it go.

Aladin

===================

Aladin/Mark

Again, thanks for your time with this. The suggestions still do not appear to be working. When I select the first cell and input the suggested formulas it changes it correctly because the first condition is correct and the date is today -1. However, when I format paint the reset of the column they all change red even though some of them do not meet the first condition (red) but meet the second condition (< TODAY -1) and should be white.

Do you think that the fact that these are automatically updating cells, would have any affect on the result. I have had to format the cells to "CUSTOM" so that the date format being received (19 JUL 2001) is understood by Excel. This format is not under the DATE format options.

Any further ideas/suggestions would be greatly appreciated.

Grant

Grant,

Care to send a (reduced) copy of the workbook or worksheet?

Aladin

===============

Aladin/Mark

I have just tried to update some cells manually using the suggested formatting and it works correctly? Now I am confused :(

Grant

: Aladin/Mark : Again, thanks for your time with this. The suggestions still do not appear to be working. When I select the first cell and input the suggested formulas it changes it correctly because the first condition is correct and the date is today -1. However, when I format paint the reset of the column they all change red even though some of them do not meet the first condition (red) but meet the second condition (< TODAY -1) and should be white. : Do you think that the fact that these are automatically updating cells, would have any affect on the result. I have had to format the cells to "CUSTOM" so that the date format being received (19 JUL 2001) is understood by Excel. This format is not under the DATE format options. : Any further ideas/suggestions would be greatly appreciated. : Grant

Aladin

How will you be able to replicate the auto-updates? The updates my spreadsheet receives are from an inhouse system. The updates are received and displayed correctly. If you have a way of replicating auto-updates onto a spreadsheet and then try to use the suggested formatting, you may be able to help me.

Grant , : Aladin/Mark : Again, thanks for your time with this. The suggestions still do not appear to be working. When I select the first cell and input the suggested formulas it changes it correctly because the first condition is correct and the date is today -1. However, when I format paint the reset of the column they all change red even though some of them do not meet the first condition (red) but meet the second condition (< TODAY -1) and should be white. : Do you think that the fact that these are automatically updating cells, would have any affect on the result. I have had to format the cells to "CUSTOM" so that the date format being received (19 JUL 2001) is understood by Excel. This format is not under the DATE format options. : Any further ideas/suggestions would be greatly appreciated. : Grant

Aladin

More info. When I manually update a cell with a CUSTOM cell format of DD MMM YYYY (which is the way the dates are received) it converts the input to lowercase dd mmm yyyy, even though I created a custom format in uppercase. Do you think this is confusing Excel? The manually added lowercase cells are changing colours nicely.

Grant

Grant -- I just tested your custom formatting and found no problems.

======== More info. When I manually update a cell with a CUSTOM cell format of DD MMM YYYY (which is the way the dates are received) it converts the input to lowercase dd mmm yyyy, even though I created a custom format in uppercase. Do you think this is confusing Excel? The manually added lowercase cells are changing colours nicely.

Maybe...

Grant,

Before you would want to send me a copy anyway, try the following modification:

=A1+0=TODAY()-1 (a zero, that is)

=A1+0 < TODAY()-1

Aladin

You don't need to use Formula Is for dates Cell Value Is will do just nicely

Use Cell Value Is_Less Than and put =TODAY()

This work for Between (or Any) eg =TODAY()-1 and =TODAY()-30

Hope this is what you after

Ian

You don't need to use Formula Is for dates Cell Value Is will do just nicely

Use Cell Value Is_Less Than and put =TODAY()

This work for Between (or Any) eg =TODAY()-1 and =TODAY()-30

Hope this is what you after

Ian