Page 1 of 1
Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 3:54 am
by JustANoob
I need some help in my formula. Here's what I need:
In column A, I have "Ticket #"
column B - "Requested Date"
column C - "Target Date"
column D - "Days Overdue"
column E - "Status"
So, for every Ticket, I have a date when it was requested (column B) and when it should be done (column C).
My formula for column D is: =IF((DAYS(TODAY(); C2))>0; DAYS(TODAY(); C2); 0)
What I need for my conditional formatting is:
1. If "Days Overdue" is more than zero, Ticket # background would turn RED, else it should start out always as BLUE. But if "Status" is "RESOLVED" (seen in Status column), background would then turn green.
2. Unless "Ticket #" column is blank, formulas won't apply YET to its rows (would appear blank as default).
3. Also, formula won't affect "merged cells".
I'm not familiar with Conditional Formatting as of yet. Hope someone could help me.
Many thanks in advance!
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 6:06 am
by RusselB
To my knowledge a conditional format has to be applied to the cell where the calculation is taking place.
ie: If you are working with calculations in column D, then the conditional formatting is applied to column D
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 7:42 am
by coray80
Click in any cell in the 'D' column and go to 'menu-format-conditional formatting' to see the formulas involved.
Note - the order of the formula is important because if the first (top) condition is True then none of the others below will even be considered!!
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 8:23 am
by Villeroy
In Corie's file: =IF(TODAY()-$C2>0;TODAY()-$C2; 0)+STYLE(IF($E2="RESOLVED";"green";IF(CURRENT()<=0;"blue";IF(CURRENT();"red"))))
does the calculation and conditional formatting on the fly showing the same values and colors as in column D.
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 8:52 am
by JustANoob
@Russel8 and coray80
Coray, I can't open the file because this desktop I'm using doesn't have an OO.Calc (it's the only one that has an internet connection). Sorry 'bout that.
Correct me if I'm wrong. What if..
A.) I highlight the entire column A, then set condition1 to "formula is" and =FIND("Resolved";E1:E100) and set GREEN as background,
B.) Set condition2 to "cell value is" to "greater than 0" and set RED as background,
C.) Set condition3 to "cell value is" "equal to 0" and set BLUE as background?
Would that work?
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 8:54 am
by JustANoob
@Villeroy
Just recently saw your post.
So I just have to post the said formula on condition1? haven't tried that one yet.
Just a question: what does the "$" sign means?
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 9:15 am
by Villeroy
Put it in D2 of "JustANoob.ods" or any other cell. Then copy down. It does not use conditional formatting.
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 9:51 am
by JustANoob
Ville, I got way-off overdue dates when trying the formula you posted.
When I tried 07/18/14 as Requested Date and 07/16/14 as Target Date, my Overdue date was 185.
Formula seems fine (today-target date). Not sure why.
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 10:16 am
by JustANoob
I tried this formula: =IF((DAYS(TODAY();C2))>0;DAYS(TODAY();C2);0)
Seems to work. Should I just add the "+STYLE(IF($E2="RESOLVED";"green";IF(CURRENT()<=0;"blue";IF(CURRENT();"red"))))"?
Tell me of your result, thanks.
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 10:35 am
by Villeroy
Tell me of your result.
Re: Multiple condition in Conditional Formatting
Posted: Fri Jul 18, 2014 10:52 am
by Villeroy
JustANoob wrote:Ville, I got way-off overdue dates when trying the formula you posted.
When I tried 07/18/14 as Requested Date and 07/16/14 as Target Date, my Overdue date was 185.
Formula seems fine (today-target date). Not sure why.
The DAYS function is obsolete. Subtracting 2 day numbers from each other does the same. All dates in spreadsheets are day numbers. In every respect they behave exactly like integer numbers. Any integer number can be formatted to be displayed as a date, any date can be formatted to be displayed as an integer number. When you subtract 2009/07/06 from 2014/07/18 you get 1838 because you subtract day number 40000 from day number 41838 actually. MONTH(41838) => 7, YEAR(41838) => 2014, DAY(41838) => 18 and you can use the DAYS function to subtract 2 numbers from each other because this is what it actually does.
Day number zero in Calc is 1899-12-30 which is mostly compatible with Excel and one of its bugs. Enter a zero into a cell and format as date.
PI() formatted as date+time => 1900-01-02 03:23:54 which is day number 3 and roughly 1/7 of a day (3,1415926536)
Re: Multiple condition in Conditional Formatting
Posted: Sat Jul 19, 2014 5:08 am
by coray80
@ JustANoob
The photo formulas will work OK in column A as well.
If you highlight a table the "Active Cell" can be in either of the four corners depending on the direction you created this area.
This "Active Cell" has to be the cell on which you base your formula ie. to use the formula in the photo the active cell should be in A2.
@ Villeroy
CURRENT is new to me thanks for the pointer.