Comparing small timestamps in conditional formatting does not give expected result

Hi there,

This is my first post here. I'm 36, from Luxembourg (Europe) and consider myself a Poweruser. I do a lot of VBA but I'm no IT guy.

Anyway, I have a problem I am unable to solve.
I'm working on a sheet which helps a manager of a Department to organize the shifts of his employees in the upcoming month. For every workday of the week, he puts in the start and endtime of the employees shift. Then, he can see if he assigned to many or to few work hours (based on a weekly due). This is done using conditional formatting. RED means not enough hours, GREEN means everytihng OK, YELLOW means too many hours assigned.

This works almost perfectly. Indeed there is one employee who only works 1 hour a week. For him, even if the sum of the different days amounts to 1 hours, the sum field is displayed in red (as if he hadn't gotten enough hours assigned).

I was able to reproduce this behaviour in a new Workbook. So my guess is, it has to do with the way Excel stores timestamps internally and not with the workbook itself.

Here are some screenshots with explanations:

Common explanations:
In the first 2 rows I specify a start and end time for the shift (in this examples, the workweek has only 2 days, isn't that great :-)) The next column shows the time worked (simply $C2-$B2)
Weekly due: this is a user inputed field in the format [hh]:mm. In my original file, this information comes from a different sheet. but for the purpose if this demonstration, I input the value by hand.
Total worked: =SUM(D2:D3). Nothing fancy here.
Diff: =D8-D7

First screenshot. Expected result. Weekly due is 3:00. Total worked is 3:00. the field becomes green.
Excel good formatting.png

Second screenshot. Unexpected result. Weekly due is 1:00. Total worked is 1:00. So the field should be green. However, it is red (meaning total worked would be less than weekly due??)
Excel wrong formatting.png

Third screenshot: something weird I noticed: If I split the 1 hour from screenshot 2 over 2 days (1/2 hour per day, amounting to 1 hour total), the conditional formatting is as expected.
Excel weird formatting.png

I do really not know where this is coming from. The only reason I see would be the internal handling of timestamps. Excel does this as float IIRC? But why wouldn't this work? I hope someone can help me out here.

If necessary, I can upload the xlsx somewhere.

Regards.

Thanks for your quick reply.

I uploaded the sample document

Thanks, that did the trick. I just had to also ROUND the value in D7.

So I was kindof on the right track thinking about the floats. But I did not think about trying to round a "Time" value.

Thanks