Conditional format on columns with formulas and custom format
Posted by Kevin Mac on December 28, 2001 9:07 AM
I have 3 columns of time tracking data, each column contains formulas in each cell and each cell has a custom format as follows:
Formula = SUM(PENDING!$B3-PENDING!$A3)
Format = 28 Days & 11 hours & 01 Minutes & 39 Seconds
I am looking for a "conditional format" for each of the 3 columns to code the cells with a certain time range a different color dependant on the elapsed time.
I have tried and tried to conditionally format the columns but I get no result from the normal conditional format. Any suggestions are appreciated.
Posted by IML on December 28, 2001 9:19 AM
Lets say you want to highlight everything over 90 days.
Under conditional formatting, select cell value and type in 90 and then your format.
Anything over 90 days 12 hours would be over 90.5 etc. Remember, formats don't have any affect, excel is still "storing" time as 1 = 1 day.
Hope that answers your question.
Good luck
Posted by Kevin Mac on December 28, 2001 10:13 AM
Follow Up on conditional format - FOR IML
That does help, however, what I am working on is VERY time sensitive and needs to be tracked in 4 hr increments, so with the supplied formula and custom format, I can not find a working conditional format. If I understand your statements above, I could conditional format based on 1/4's of the day????
Posted by IML on December 28, 2001 10:53 AM
Re: Follow Up on conditional format - FOR IML
Lets say you want three condintional formats, over 4 hours, over 8 and over 12
Do your longest time as the first conditions, down the shortest as number 3
anything over 12 hours is > .5 (12 hours/24)
anything over 8 hours is >.333333 (8/24)
anything over 4 hours is > .166667 (4/24)
Is that what you mean? I'm a little confused because you refer to both four hour periods and quarters of a day.
Posted by Kevin Mac on December 28, 2001 10:54 AM
Works like a champ
That got it, works like a champ, Thanks for the assist, you guys are great!!!
