You are here:

Excel/Condional Formatting - Time



I have a column of times in the format hh:mm:ss, and I want to signify by colour if each time is above or below the average time.  I know how to apply conditional formating but it doesm't appear tp work with time.  Regardless of whether the relevant time is above or below the average, whatever colour I programme  first (green for above & red for below average) is returned for all times regardless

Thanks for any help or advice you can offer.

ANSWER: You'll need to work out your formula as TRUE/FALSE constructs in a regular cell first.  Once you have them properly giving you TRUE/FALSE answers when applied to your data, you can then slip them into Conditional Formatting.

Also, in a cell, you can run the Formulas > Evaluate Formula function and watch your formula and see what it's doing, possibly SEE why it's not calculating the way you want.

So, if you have a time value in A1, then this formula will test that value against the "timevalue" of your "average".  Let's say the average time is 2 hours.

=AND(A1>0, A1<=TIMEVALUE("2:00:00"))    (green)
=AND(A1>0, A1>TIMEVALUE("2:00:00"))     (red)

Does that help?

---------- FOLLOW-UP ----------


Firstly, thank you very much for your reply, it is very much appreciated.

However, I feel that I did not fully explain the circumstances surrpunding the data I am trying to convert into colour by the use of conditional formatting.  I am a relative novice using Excel and if this seems a little simplistic then I apologise.

The data that I wish to convert is drawn from another spreadsheet therefore, the relevant cells already have a formula within the them.  I don't know if or how your formula could be added to the presnt formula within the cell?

Once again, thank you so much for your assistancer.

ANSWER: I'm sorry I wasn't clear in my suggestion.   Of course you would be putting your CF formulas for testing in an EMPTY cell on the same row with the data you're testing.  In the example given previously we were testing the CF formula we would LATER expect to put ON the cell A1, but to test that same formula in a cell I'd move over to B1 or C1 or to some empty cell in row 1 and play with my formulas there until I'm getting the correct TRUE/FALSE results.

Once the formula is working correctly in an empty cell, I would THEN take it and apply it to the actual cell in the CF setting.

---------- FOLLOW-UP ----------


Firstly, sincere apologies as it wasn't my intentionpto appear ungrateful.  In fact, I am extremely appreciative of your help and guidance so far.

I have tested uour suggested formulae and,  indovidually, they return the correct true or false result including when I tag them on to the existing formula in the cell.  What I have been unable to work out is how borh the true and false formula can be added to existing formula and still give the correct true or false result?

I hope the above makes sense.  Once again, thank you so much for your help.

No, you will not be tagging these formula onto the existing formulas in the cell.  You will be testing them in another empty cell on the same row.

Once they are working correctly, you will click on the cell you want them to apply coloring to, then you will open the Conditional Formatting wizard and add those formula into CF rule there.  Not IN the cell,  ON TOP of the cell through the CF wizard.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.