Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

Conditional formatting with dates

Posted by Grant Shrosbree on July 19, 2001 4:47 AM
I am trying to get a column of auto-update cells to change colour when the date received by these cells meets certain criteria. If the date is yesterday then I wish the cell to be, say red. If the date is older or doesn't update then I wish it be blue.

I have tried Conditional Formating using the =TODAY()-1 formula and changing the date format to Custom (the date is received in the format 18 JUL 2001. These do not appear to work correctly.

Thanks in advance

Grant


Re: Conditional formatting with dates

Posted by Mark W. on July 19, 2001 7:15 AM
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.


Re: Conditional formatting with dates

Posted by Grant Shrosbree on July 20, 2001 12:46 AM
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


Re: Conditional formatting with dates

Posted by Aladin Akyurek on July 20, 2001 1:42 AM

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

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


Re: Conditional formatting with dates

Posted by Grant on July 20, 2001 3:11 AM
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


Re: Conditional formatting with dates

Posted by Aladin Akyurek on July 20, 2001 3:23 AM
Grant,

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

Aladin

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


Re: Conditional formatting with dates

Posted by Grant on July 20, 2001 3:25 AM
Aladin/Mark

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

Grant


Re: Conditional formatting with dates

Posted by Grant on July 20, 2001 3:26 AM
: 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


Re: Conditional formatting with dates

Posted by Grant on July 20, 2001 3:29 AM
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


Re: Conditional formatting with dates

Posted by Grant on July 20, 2001 3:44 AM
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


Re: Conditional formatting with dates

Posted by Aladin Akyurek on July 20, 2001 5:15 AM
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...

Posted by Aladin Akyurek on July 20, 2001 5:22 AM
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


Re: Conditional formatting with dates

Posted by Ian on July 22, 2001 2:56 PM


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


Re: Conditional formatting with dates

Posted by Ian on July 22, 2001 2:57 PM


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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.