conditional formating to change cell colour when time has reached 30 mins then 60 mins

How can I set a conditional format for a cell so that it changes colour once it has passed 30 minutes and then when it has passed 60 minutes.

Column Time entered - time put in when something is received. If it is not dealt with within 30 minutes of time received it will turn amber and if it is not dealt with within 60 minutes it will turn red. Once a time has been entered in to the next column which is time dealt with, the time entered cell will turn green.

I can set this for one change i.e. red by this formula =$G6:$G346+TIME(1,0,0)<(NOW()-TODAY())

and Green by this formula = =LEN($H6:$H346)>0

but not sure how to add another one so that at 30 minutes it turns amber and then at 60 minutes it turns red.

Hope that makes sense

Hi,

Where your time is in A5 try,

Amber =AND(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))>=$A$5+"00:30:00",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))<=$A$5+"00:59:59")
Red =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))>$A$5+"00:59:59"

HTH

Steve

Thanks, that worked, had spent practically all day trying to figure out a way to do that!