Forum Discussion
Conditional Formatting Depending on Time Lapsed
Hi all,
Can anybody provide any advice on how to create conditional formatting based on due time (not date)? I.e. if the cell in column H doesn't say 'completed' within 3 hours of the deadline the row becomes orange, then red over 3 hours?
Many thanks in advance!
Which column contains the deadline?
And does it contain just the time or the date plus the time?
Thank you for your quick reply. Column G will have the deadline – for now it contains just the time (with the assumption that the task will be completed 'today').
Let's say the data begin in row 2.
Select rows 2 to 100, or however far down you want.
The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND($G2<>"",$G2+TIME(3,0,0)<MOD(NOW(),1),$H2<>"Completed")
Click Format...
Activate the Fill tab.
Select red
Click OK, then click OK again.
