Conditional formatting using Hour values

Question

I am trying to create a Conditional Formatting formula to be applied along two columns comparing HOUR values. As an example I have below three rows of the spreadsheet (the link to the actual spreadsheet is here):

   |     A     |     B    |    C    |   D    |
1  |   14:00   |   02:00  |  12:00  |  12:00 | 
2  |   15:30   |   16:30  |  01:00  |  01:00 |
3  |   08:30   |   09:30  |  01:00  |  01:00 |

Step 1: The C column is a result of a subtraction: B - A. Since I am working with Hour values (not Date and Hour nor Date, only Hour) there is a formula that I use on the C column that is:

      =(B1-A1) + IF(B1 < A1; 1)

This formula essentially calculates B - A and if B is "smaller" than A (it actually is a timespan from the next day), it adds 1, otherwise my result would be a negative amount of hours. (More about that here.)

Step 2: The D column is an inserted value (not the result of any formula), and the whole column has the format (Hour).

Step 3: I created a Conditional Formatting rule that compares the values on the C column with the values on the D column. If they are different, the cells on the C column should be painted green:

=C1<>D1 on the interval C1:C1000

Step Problem:

The C1 cell isn't painted, since its value is the same of the D1 (both are 12:00).
The C2 also isn't painted since its value is the same of the D2 (both are 01:00).
The C3 is painted even though its value (01:00) is the same of the D3 (01:00)!

And that behavior just goes on repeating itself apparently randomly on the spreadsheet.

I've researched a lot about it and even tested it on Excel and found that there it works like a charm. But in Google Spreadsheets I get this strange behavior.

score 2 · Answer 1 · answered Mar 12 '15 at 05:35

2

Very confusing but fortunately very easy to fix (I think!). Please try:

=round(D1;14)<>round(C1;14)

instead of just =D1<>C1.

answered Mar 12 '15 at 05:35

pnuts

58,317
11
87
139

An equally valid answer to mine, and probably much easier to implement. – AdamL Mar 12 '15 at 05:37
Although, might be slightly safer: `=FLOOR(D1;0,00000000001)<>FLOOR(C1;0,00000000001)` to cater for edge case where one rounds up and the other down (which, perhaps, mathematically may never happen). – AdamL Mar 12 '15 at 05:40
2

Pnuts your solution worked too but i can only mark one awnser as correct. Thank you guys. – William Satoru Mar 12 '15 at 18:52

score 1 · Accepted Answer · edited May 23 '17 at 12:06

The behaviour you are seeing is a result Google Sheets' treatment of floating point errors (you could argue that Excel, in this respect, does the job better, as it will more often mask these errors).

You can see what is going on by changing the format of columns C and D to Number, and increase the number of displayed decimal places to about 16 (using the .00-> button), where you will see the discrepancy.

The workaround is to perform some rounding on the calculated data, which is easier said than done. If column D only contains times containing whole minutes, you could use:

=ROUND((B1-A1+IF(B1<A1;1))*1440)/1440

which incidentally can be shortened to

=ROUND(MOD(B1-A1;1)*1440)/1440

Conditional formatting using Hour values

2 Answers2