I want to set a conditional format in a column to highlight anything red that is greater than the date of 12/24/2007. I have set the field as a Date field. I have set the conditional format correctly. However, it highlights everything red until I double-click on a cell and then it actually makes the correct conditional format. Since I have thousands of cells to double-click on I do not have time to manually do this. I think it is reading the format of the field as a text field until I double-click it and then it reads the date field. I have exhausted all my known resources. Does anyone know what is going on with Excel to cause this?
It sounds like you are correct and the cells were initially set as text fields and you'd need to manually enter each one so that it recognizes the new formatting. (This has nothing to do with your conditional formatting except that your CF doesn't recognize it because it's text).
To fix, try this.
in an empty cell somewhere(make sure the cell is formatted as general), type 1 and copy . highlight all your date cells and paste special > multiply. Does that fix your problem?
ChemistB
As a second option, you could also change the conditional format formula. If the first date is in A2 and 12/24/07 is in C1, =A2+0>C$1. Adding 0 (some prefer multiplying by 1) causes excel to treat numbers and dates stored as text as numbers. If the dates are coming from another source (seems likely, as Excel is usually good about changing the format to date appropriately), and these values are copied and pasted into your workbook regularly, this approach has the advantage of being a one-time fix, but also the disadvantage that any formulas involving subtracting one date from another will also not work without adding 0 to them first or changing them as ChemistB suggests.
Last edited by darkyam; 03-21-2008 at 01:06 PM.
Thanks for the help. I actually didn't get to try your suggestions because I found a work around before I got to check back on this thread. I found that if I copied the cells into Notepad and then copied them back from Notepad into Excel I was able to format them correctly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks