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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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,122,454
Messages
5,596,228
Members
414,046
Latest member
mbeutler1203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top