Conditional Formatting using a time limit?

[Legacy 50109]

Hi there,

I am looking to create a tracking sheet, that would require a row of cells to change color after 30 minutes from the time the 1st series of text is inputted.

For example, I will have 2 columns. The 1st column is when I send out the documents, lets say is 2pm, but in order to keep the flow going smoothly, I need to receive a response within 30 minutes. The second column is when I receive it. If I don't receive a repsonse within 30 minutes I need the row to change color.

Is this possible, with the use of conditional formatting?

Thanks, David

 

[bk]

Do a search of the archives for

Start Timer

There is some useful code on how to keep a timer running. Once you have that, the Cond. Formatting code is pretty easy.

Note: Use of this timer will require you to keep a Macro running at all times (if this is to work). So, you won't be able to use another Excel file while this is happening. This could be a problem, depending on your situation.

 

[wwbwb]

Create a third column. Use the following formula in each of the sent/received rows.

=IF(B2="",IF((A2+1/48)<=TIME(HOUR(NOW()),MINUTE(NOW()),),"late",""),"")

It will show "late" in the third column if nothing has been received within 30 min. Now do conditional formatting on your rows to change color if the third column says "late".

Book1.xls
	A	B	C	D
1	Sent time	In time	late?
2	9:28	9:30
3	9:15
4	9:00		late
5	8:30	8:45
6	8:45		late
Sheet2