Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting of date-cell - 2007
I would like to conditional format cells - in colors - containing a date if
date in cell is before or after TODAY(). This is not - strangely - an option in the general Highlight date occurring... CF of Excel2007. How do I do this, then? I have tried greater/less than but it did not work. Additional question: I tried one of the possible options (Yesterday) this worked with date format dd-MM-yy but when I changed format to yy-MM-dd (which I would like to use) it did not work. How come? Any help appreciated! -- Best regards Ulf |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting of date-cell - 2007
Select the range cell (say cell A1)
Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. 'if not today =AND(ISNUMBER(A1),A1<TODAY()) 'if date greater than today =AND(ISNUMBER(A1),A1TODAY()) -- Jacob (MVP - Excel) "UlfHJensen" wrote: I would like to conditional format cells - in colors - containing a date if date in cell is before or after TODAY(). This is not - strangely - an option in the general Highlight date occurring... CF of Excel2007. How do I do this, then? I have tried greater/less than but it did not work. Additional question: I tried one of the possible options (Yesterday) this worked with date format dd-MM-yy but when I changed format to yy-MM-dd (which I would like to use) it did not work. How come? Any help appreciated! -- Best regards Ulf |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting of date-cell - 2007
Hello Jacob,
Much as I appreciate your answer, I cannot ake it work. Am I missing something? P.S. On the second question I had I found a cause. Human in origin ;-) -- Best regards Ulf "Jacob Skaria" wrote: Select the range cell (say cell A1) Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. 'if not today =AND(ISNUMBER(A1),A1<TODAY()) 'if date greater than today =AND(ISNUMBER(A1),A1TODAY()) -- Jacob (MVP - Excel) "UlfHJensen" wrote: I would like to conditional format cells - in colors - containing a date if date in cell is before or after TODAY(). This is not - strangely - an option in the general Highlight date occurring... CF of Excel2007. How do I do this, then? I have tried greater/less than but it did not work. Additional question: I tried one of the possible options (Yesterday) this worked with date format dd-MM-yy but when I changed format to yy-MM-dd (which I would like to use) it did not work. How come? Any help appreciated! -- Best regards Ulf |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting of date-cell - 2007
Try
1. Suppose you have dates in A1:A10. Select the cell/Range (say A1:A10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula 'if not today =AND(ISNUMBER(A1),A1<TODAY()) 'if date greater than today =AND(ISNUMBER(A1),A1TODAY()) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. -- Jacob (MVP - Excel) "UlfHJensen" wrote: Hello Jacob, Much as I appreciate your answer, I cannot ake it work. Am I missing something? P.S. On the second question I had I found a cause. Human in origin ;-) -- Best regards Ulf "Jacob Skaria" wrote: Select the range cell (say cell A1) Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. 'if not today =AND(ISNUMBER(A1),A1<TODAY()) 'if date greater than today =AND(ISNUMBER(A1),A1TODAY()) -- Jacob (MVP - Excel) "UlfHJensen" wrote: I would like to conditional format cells - in colors - containing a date if date in cell is before or after TODAY(). This is not - strangely - an option in the general Highlight date occurring... CF of Excel2007. How do I do this, then? I have tried greater/less than but it did not work. Additional question: I tried one of the possible options (Yesterday) this worked with date format dd-MM-yy but when I changed format to yy-MM-dd (which I would like to use) it did not work. How come? Any help appreciated! -- Best regards Ulf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
Excel 2007: cell shading (not conditional formatting) | Excel Discussion (Misc queries) | |||
conditional formatting date and blank cell | Excel Discussion (Misc queries) | |||
Excel 2007 conditional formatting & cell colour. | Excel Worksheet Functions | |||
Apply conditional formatting to individual cell Excel 2007 | Excel Worksheet Functions |