Conditional formatting of date-cell - 2007
in [General]
|
Prev: bahttext
Next: Simple Yes/No drop down box
From: UlfHJensen on 21 Apr 2010 04:39 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
From: Jacob Skaria on 21 Apr 2010 05:14 Select the range cell (say cell A1) Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use 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),A1>TODAY()) -- 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
From: UlfHJensen on 21 Apr 2010 06:01 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 tab>Styles>Conditional Formatting>Manage rules>New rule>Use 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),A1>TODAY()) > > > -- > 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
From: Jacob Skaria on 21 Apr 2010 06:05 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 Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and enter the below formula 'if not today =AND(ISNUMBER(A1),A1<>TODAY()) 'if date greater than today =AND(ISNUMBER(A1),A1>TODAY()) 4. Click Format Button>Pattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use 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 tab>Styles>Conditional Formatting>Manage rules>New rule>Use 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),A1>TODAY()) > > > > > > -- > > 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
From: UlfHJensen on 21 Apr 2010 07:46
Hello again. I thought I was experienced in my knowledge of Excel, but it appears not. I have really tried and tweaked your formula e.a. but I cannot make your suggestion work. If I understood you correctly the "A1" in your formula is the cell reference? I tried under the above assumption and it only turned my cell [red] in the event the date I entered was today. Hmmm... -- Best regards Ulf "Jacob Skaria" wrote: > 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 Format>Conditional Formatting> > > 3. For Condition1>Select 'Formula Is' and enter the below formula > 'if not today > =AND(ISNUMBER(A1),A1<>TODAY()) > > 'if date greater than today > =AND(ISNUMBER(A1),A1>TODAY()) > > 4. Click Format Button>Pattern and select your color (say Red) > > 5. Hit OK > > PS: If you are using XL2007 Goto Home tab>Styles>Conditional > Formatting>Manage rules>New rule>Use 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 tab>Styles>Conditional Formatting>Manage rules>New rule>Use 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),A1>TODAY()) > > > > > > > > > -- > > > 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 |