Help with conditional formatting a cell if its date is older than 2 hours.

This should be easy to someone smarter then me, but its whipped my butt =o)

My explanation may be overkill, but I want to make sure Im clear.

I have cells A1 to H151 reserved, and blank. I copy a form from another place(website), and paste it in cell A1. When I do this, those reserved cells fill with information. I then have cells M1-X53 preset with formulas to take information from cells A1-H151, and display what information is needed the way I want it to display. Basically this spreadsheet takes a poorly written form, and organizes it in a more efficient way.

Column W tells the time/date from the form for each individual rows data. For example..

W2 displays the forms data that was found on F2.
W5 displays the forms data that was found on F5.
W50 displays the forms data that was found on F50.
And so on and so forth.

I have formatted the cells in column W to take the original format (1/1/2014 18:45) from the form, and display it (6:45 PM)

Here is where I need your help guys!!

This form updates every half hour, and I re-feed the form when I need to get current "readings"... However, sometimes one of the rows wont update, and will still be the reading the time from earlier. Sometimes this can happen for MANY hours.

I would love your help in figuring out how to have my spreadsheet look at the time in column W for each row, and if it is >= 2 HOURS OLDER than RIGHT NOW, then to turn that whole row (from M-X) RED

Can this be done? If so I would desperately appreciate your help! Ive already wrote another format to turn the row black if a different column returns value of 0, and it works perfectly, but figuring out the whole compare the time thing is more then I can figure out.

Thank you in advance!!!!

-Shane

Hi Shane

select the range of cells you want to apply the conditional format to, then in conditional formatting apply this formula:

Please Login or Register  to view this content.

(assuming your selection starts in cell W1 - otherwise modify to indicate the first row)

then select the fill colour you want

This will not update automatically, so you will need to press F9 or otherwise recalculate the sheet for the formats to kick in

ok im confused now... this code works perfectly if I create a new sheet, and test it out... but it doesn't in my sheet... what could be wrong?

i changed the code to $W2 since the first cell to be analyzed is w2

for example:
form cell with actual time stamp = F1
display cell is W2, the formula in that cell says "=F1" <-----this is the cell I want to CF
so I highlight w2, click CF, New Rule, "use formula to determine cells to format, paste "=$W2<MOD(NOW(),1)-1/12", select format background of red, and OK

its not hurting that i have another rule target in another column is it?

correction, now its not working at all on my test sheet either, and clicking that didn't do anything

preparing it now

sdsadasd.xlsx

Does that work?

thanks for looking, but no... that changes regardless of the time.

a no go on that too... any other ideas? (and thank you)

Im working on a formula, but it just isnt working..


How do I say..

if x <= (NOW()-TIME(2,0,0)) then change background

in excel lol!

im sorry buddy, but it didnt work. I do appreciate your help though.

I ended up having to make a cell with "=now()-time(2,0,0)", making the text clear, and referencing it with CF. For Ex:

In cell J1 I put

=now()-time(2,0,0)

Then in the Display section I wrote a CF saying

=$W2<$J$1

Then I applied that to my entire display section... and BINGO! it works like a champ!!!

Thank you guys for all your help!!!