|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 21st, 2010, 09:39 AM
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
April 21st, 2010, 10:14 AM
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),A1TODAY()) '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
April 21st, 2010, 11:01 AM
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),A1TODAY()) '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
April 21st, 2010, 11:05 AM
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),A1TODAY()) '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),A1TODAY()) '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
|
|
#5
April 21st, 2010, 12:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting of date-cell - 2007
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 FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula 'if not today =AND(ISNUMBER(A1),A1TODAY()) '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),A1TODAY()) '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
|
|
#6
April 21st, 2010, 01:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting of date-cell - 2007
You must be having some format issues...Short-cut to enter todays date is
Hit Ctrl and ; together -- Jacob (MVP - Excel) "UlfHJensen" wrote: 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 FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula 'if not today =AND(ISNUMBER(A1),A1TODAY()) '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),A1TODAY()) '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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
