MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!!!