Help with Conditional Formatting

[FaitherDave]

Hey guys.

I've attached a portion of a spreadsheet I'm hoping to get help on.

In cell E6 I would enter the time a particular incident was created. I'm looking to use conditional formatting to turn that cell red if the incident runs longer than 9 hours.
Cell A3 has the =NOW() formula in it to get the current time. Next to that in cells B3, C3 and D3 I have the time +15 mins, +30 mins & +60 mins.

I have tried absolutely everything I can think of and have spent the last few hours scouring the web trying to find a solution but it just doesn't seem to work.

I was going along the lines of =IF($E6<(NOW()-TIME(9,0,0)),1,0) but this didn't work. I also tried adding a formula into cell E3 showing the time minus 9 hours, and if cell E6 was less than E3 it would turn red, but strangely this didn't work either.

Any help would be appreciated.

Thanks.
Dave

 

[Joe4]

Welcome to the Board!

In cell E6 I would enter the time a particular incident was created. I'm looking to use conditional formatting to turn that cell red if the incident runs longer than 9 hours.

This 9 hour measurement, is there another cell to determine the "end time" which you want to compare cell E6, or will it always be from the current time?
If always from the current time, you can use this Conditional Formatting Formula:

=NOW()-E6 > (9/24)

 

[Toadstool]

Hello FaitherDave,

Try this:

FaitherDave.xlsx
	A	B	C	D	E
3	4:35 PM
4
5					Incident Creation
6					23-Apr-21 7:34
Sheet1

Cell Formulas
Range	Formula
A3	A3	=NOW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6	Expression	=$E6-$A$3+TIME(9,0,0)<0	text	NO

 

[FaitherDave]

Neither of those are working

It's just turning the cell the formatted colour, regardless whether I put the a time 2 hours old or a time 10 hours old, if that makes sense?

 

[Joe4]

Neither of those are working

It's just turning the cell the formatted colour, regardless whether I put the a time 2 hours old or a time 10 hours old, if that makes sense?

Can you answer the question I asked in my previous post?
And provide an actual example where you show/tell us exactly what you are entering in to what cells?

 

[Toadstool]

Neither of those are working

It's just turning the cell the formatted colour, regardless whether I put the a time 2 hours old or a time 10 hours old, if that makes sense?

I don't know what to recommend. It works fine for me as I watch it tick over to 9 hours.