 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I want to use Conditional Formatting. I opt for "formula". The > condition should be that a cell F9 that is formatted to Date 14-Mar-07 [quoted text clipped - 9 lines] > Bart > Excel 2003 > =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) > [quoted text clipped - 19 lines] > > Bart > > Excel 2003 Hi, this solution did not work for me. May be I was not so clear and I realised there is another condition. This is what I would like: Column: F G H --> Z 9 (Date) (amount) 10 (Date) (amount) etc. etc. Dual conditional formatting: Cell range G9-20 - Z9-Z20 : Cell fill color become orange if any cell is filled with amount Cell range G9-20 - Z9-Z20 : Cell fill color become green if any cell is filled with amount AND the date column is filled with a date In other cases cell does not get fill color. Bart >> =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) >> [quoted text clipped - 41 lines] > > Bart Condition 2: =ISNUMBER(G9) Condition 1: =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) > "AAArens" <bartvandon...@gmail.com> wrote in message > [quoted text clipped - 54 lines] > Condition 1: > =AND(ISNUMBER(G9),LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) Bob, I can't get it working. I filled both conditions in cell G9, first condition 1 then 2. But only the format I have assigned to condition 2 is visible if G9 has data, whether F9 is filled in or not. Bart It might be because I didn't fix F9 down using absolute referencing. I have posted an example at http://cjoint.com/?bDnSVGsfR1 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> "AAArens" <bartvandon...@gmail.com> wrote in message >> [quoted text clipped - 61 lines] > > Bart > It might be because I didn't fix F9 down using absolute referencing. > [quoted text clipped - 77 lines] > > > Bart Hi Bob, it works fine, triggy idea. I also hided the second date column. As the cells right of the date need to chege colors, I changed $F$9 into $F9. Just a small add. I also have a combo column left of the date column where I can choose the currency either USD, EUR, or IDR. How to have the cells that are filled in the G-Z area also have italics in case I choose EUR. In fact there should be a third condition beside the one we discussed before. What is this condition? Bart Bart, Is that 4 conditions? Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is italicised if any cell is filled with amount AND currency is EUR Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is italicised if any cell is filled with amount AND the date column is filled with a date AND currency is EUR Cell range G9-20 - Z9-Z20 : Cell fill colour become orange and amount is not italicised if any cell is filled with amount AND currency is not EUR Cell range G9-20 - Z9-Z20 : Cell fill colour become green and amount is not italicised if any cell is filled with amount AND the date column is filled with a date AND currency is not EUR In other cases cell does not get fill colour. If so, that becomes tricky as CF only as 3 conditions. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> It might be because I didn't fix F9 down using absolute referencing. >> [quoted text clipped - 94 lines] > > Bart > Bart, > [quoted text clipped - 121 lines] > > > Bart Hmm, if an additional format of a cell value should be italics when a combo value from another column is set to EUR, I think its one extra condition because the extra format applies independently of the "orange -" or "green condition". Bart Okay, what column is the currency in? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Bart, >> [quoted text clipped - 137 lines] > > Bart > Okay, what column is the currency in? > [quoted text clipped - 155 lines] > > > Bart Goodmorning. The currency combo box column is located left of the date column. So I have: Currency (D) - Date (E) - Date (hided column, for triggering) (F) - G/Z are columns with invoice value G/Z cells are either blank (empty) or, if not empty: - they are green/orange (depend if date cell is filled with date) -> Works fine! - eventuelly also italics depend if chosen for currency EUR Bart |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.