• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting hours minutes

6

6digit

New Member
I have a simple spreadsheet that takes hours/minutes input on a monthly basis in the format of [h]:mm

What I am trying to do is use conditional formatting so if the hours are between 0:01 and 5:00 the cell is one color, and if greater than 5:00 the cell is a different color. I have tried several methods but at the moment, I can only format based upon a greater than 5:00 situation which excel converts to a decimal number.

My latest attempt was two conditions: if cell value is between 0:01 and 4:59 (format) and if cell value is greater than 4:59 (format)

Excel converts both of these to decimal numbers but they do not work as desired. Any help would be very much appreciated.
 
Format all the cells to a single colour (say amber), now do conditional formatting >0.20833333 should go red ( whatever colour you like)


Now all timings between 0:01 to 04:59 would be of amber and greater than 5:00 would be red...


to know your number you can change the format of the cell to number and then again change the fomat as desired..
 
Thank you for responding. My cells were all formatted to a shade of red to indicate no activity in them. But I need two conditions beyond that: the first with a between at which point they turn yellow and the second with a greater than at which point they turn green. When I try this, only the greater than condition will work.

BTW this is Excel 2003.
 
Hi ,


If you need to format several cells with the same threshold value , I suggest you put this threshold value in some cell , say H3. Put the value 05:00 in H3.


If you now wish to CF cell A7 , use the following formula :


=A7<$H$3 format the color Amber


=A7>=$H$3 format the color Green


Narayan
 
OK that's almost got it, but using those two formulae leaves the cell amber if no value is entered in it. Otherwise it works fine. What I'm trying to do is have the cell remain red if no value, change to amber if between two values, then green if greater than.


Also with the formulae, how can I apply it to a row of cells?


Thank you for your help.
 
Hi ,


I am not clear on how the cell can be Amber when no value is entered in it.


In case you wish to have similar formatting in other cells , select A7 , click on Format Painter , and click on the range you wish to format.


Narayan
 
The cell does not turn amber UNTIL a value is entered into it. When there is no value, it is red. I simply want two conditions by formula as stated above. Another way of saying it is red when no value has been entered, amber when a value less than a required number is entered, and green when the required number is met or exceeded.


Thanks again.
 
Modifying Narayank's suggestion slightly:


=AND(A7>0,A7<$H$3) format the color Amber


=A7>=$H$3 format the color Green


That should stop the blank cells from being colored.
 
Hi, 6digit!

Check this:

http://www.2shared.com/file/5yqFr5pu/Conditional_Formatting_hours_m.html

In the conditional format definition it has set the time value to 1h 0m 0s. You may want to change it from inside that definition, or use cells as parameters.

Regards!
 
You must log in or register to reply here.
Back
Top