Conditional Formatting using a time limit?

L

Legacy 50109

Guest
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

bk

Active Member
Joined
Jun 2, 2002
Messages
387
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

Well-known Member
Joined
Oct 20, 2003
Messages
513
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
ABCD
1Sent timeIn timelate?
29:289:30 
39:15 
49:00late
58:308:45 
68:45late
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,118,735
Messages
5,573,916
Members
412,555
Latest member
mark84
Top