-1

I need help with a Google spreadsheet formula. This is what I'm trying to do:

Highlight the cells in “Conditional Formatting” where the time spent between 2 cells is more than 60min. In this case the B cell should be highlighted because the activity stopped for more than an hour.

activity stopped for more than an hour

Which formula to use? I try to change time format to number and compare between the cells but no success. Any idea please?

|improve this question
  • All the time is for the same day, the B is the 11:59:00 The downtime between 10:11:32 and 11:59:00 is more than 60 min so the cell 11:59:00 should be highlighted. Thanks – La_dh Jun 28 '17 at 12:34
0

To avoid the possibility of conflicting rules, clear all conditional formatting from ColumnA (assumed because OP won't say where the data is), select it and apply a Custom formula is of:

=and(A2<>"",A1>1/24+A2)

Select your format and Done.

For your locale you might need ; rather than ,.

AND (used here to address blank cells).

With one day being one unit to Google Sheets, 1/24 is one hour.

|improve this answer
  • That is very nice, thanks a lot! I was playing with it and let me ask few things more, it's not working when the midnight is passed. 0:35:01 22:02:42 (the 0:35:01 is not highlighted.) Any way to fix it ? – La_dh Jun 28 '17 at 15:46
  • ok, i will separate from 0:00 thanks! :) – La_dh Jun 28 '17 at 15:58
  • Can i add a period of time like 1/24 - 2/24 ? What i want is to mark with different color the downtimes between 1-2 hours. – La_dh Jun 28 '17 at 16:11
  • Downtime 1-2hours yellow, downtime 2-3hours red. Something like =and(A2<>"",A1>=1/24+A2) add new formula =and(A2<>"",A1>=2/24+A2) with different color. Only highligts yellow color. – La_dh Jun 28 '17 at 16:50
  • It seems ok but no color change, yellow remains yellow more than 1hour =and(A2<>"",A1>=1/24+A2) more than 2 hours : =and(A2<>"",A1>=2/24+A2) no highlights the cells to red (stay on yellow) where 2hours past. Any idea? thank you – La_dh Jun 28 '17 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.