Hours a staff member works each day formula returning unexpected result

**lukela85** · 09-15-2013, 11:07 AM

Hi all,

I'm working on a work rota.

I have written a formula which works out how many additional hours a staff member works each day. However in some circumstances the formula is returning a negative result when I'm expecting a 0 return.

I've attached the file with notes. Apologies for my untidy formulas in advance!

Many thanks,

Luke

**Tsjallie** · 09-15-2013, 02:46 PM

Not sure about this, but I have the feeling that the following is happening.
Internally time is represented by a number with 15 decimals. Apparently sometimes these numbers (like with 22:00 hrs) are rounded to 15 decimals, but somehow the remaining decimals are still there.
Now 22:00 hrs is represented by the 0,916666666666667 which (because ending with 67) looks like being rounded upwards. So actually smaller than represented.
What you then get is 0,916666666666667 (22:00) minus 0,770833333333333 (18:30) minus 0,145833333333333 (3:30) should leave 0 but in fact leaves -0,0000000000000000832667268468867. Being the difference between the representation of 22:00 hrs and it's actual value.

Solution is pretty simple:

ROUND(IF(G14="HOL","",IF(G14="BH","",H14-G14-'Hours Matrix'!$D2-IF(AND(G14<0.35416667,H14>0.60416667),1/24,0))),15)

**BadlySpelledBuoy** · 09-15-2013, 03:52 PM

Hi,

The above is definitely a nice simple solution to this so I would use that, but just thought I'd say the section in the middle of your formula that refers to the Hours Matrix worksheet could be replaced with an INDEX/MATCH to automatically look up the values you're manually referring to. This would negate the need to adjust each formula individually as you could just write one and then copy it to all the other cells.

You could also turn the two nested IFs at the start into an IF/OR.

BSB.