Highlighting cells in Excel
My data entry person has to enter a column with row after row of times (hours, minutes and seconds). This is prone to error. Is there a way I can highlight a row if either of the following is true:
1. The time on the current row is more than 2 minutes before or after the time on the row immediately preceding it
2. The number of seconds is not 0, 15, 30 or 45.
I have used conditional formatting before, but I can't seem to get these two to work.
Thank you.
1. The time on the current row is more than 2 minutes before or after the time on the row immediately preceding it
2. The number of seconds is not 0, 15, 30 or 45.
I have used conditional formatting before, but I can't seem to get these two to work.
Thank you.
ASKER
Attached is a sample
there are mistakes in rows 7 (16 seconds instead of 15) and 11 (time is more then 2 minutes before/after previous row).
I would want columns A and B to be highlighted if either mistake is made
Sample.xlsx
there are mistakes in rows 7 (16 seconds instead of 15) and 11 (time is more then 2 minutes before/after previous row).
I would want columns A and B to be highlighted if either mistake is made
Sample.xlsx
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
...there are mistakes in rows 7 (16 seconds instead of 15)...
Cell [B7] contains 06:44:31.
Did you attach the correct workbook?
Stick this formula into a hidden column and do conditional filtering on it
=IF(ABS((A2-A1)*1440)<=2;IF(SECOND(A2)=0;"Valid";IF(SECOND(A2)=15;"Valid";IF(SECOND(A2)=30;"Valid";IF(SECOND(A2)=45;"Valid";"Not"))));"Not Valid")ASKER
The times in column "B" must follow these rules:
1. seconds must be 0, 15, 30 or 45
2. Time must be within 2 minutes of the previous row.
B7 is 31 seconds, which is not 0, 15, 30 or 45, and so the row should be highlighted.
1. seconds must be 0, 15, 30 or 45
2. Time must be within 2 minutes of the previous row.
B7 is 31 seconds, which is not 0, 15, 30 or 45, and so the row should be highlighted.
ASKER
The code generates an error when I type it in (I had to adjust the column from "A" to "B" but it is otherwise the same.
=IF(ABS((b2-b1)*1440)<=2;IF(SECOND(b2)=0;"Valid";IF(SECOND(b2)=15;"Valid";IF(SECOND(b2)=30;"Valid";IF(SECOND(b2)=45;"Valid";"Not"))));"Not Valid")
Change the ";" to ","
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. This works perfectly, and now I will be able to easily spot data entry errors.
Is the format of the entry hours:minutes:seconds or "hours, minutes and seconds" as you stated above?
Perhaps supplying a sample workbook would help those wishing to help you.