Excel makes a weird calculation mistake

Hello,

i'm making a time sheet for work and i wrote these equations

input : arrival time , departure time , working hours , limit

cell#1 : duration = departure - arrival

cell#2 : deducted = ceiling(working hours - duraion,limit)

working hours = 8:00
limit = 00:15

for some reason when and only when i use these values (9:50 for arrival) and (17:50 for departure)
cell#2 returns the value (00:15) instead of (00:00)

as if (8:00) - (17:50 - 9:50) is more than zero

example in attached file
MS office 2007

HELP

AttachmentSize
error.xlsx9.76 KB

The Ceiling function will

The Ceiling function will always round UP to the next multiple of your "significance" which in this case is your limit. to counter act that just add -D6 to the end of your function as follows:

=CEILING(D5-D9,D6)-D6

Regards
Mark

No CEILING function is not

No CEILING function is not supposed to round up zero

to make sure try values like (9:40 for arrival) and (17:40 for departure)
or any values that returns the duration to (8:00)

it won't round up the zero

Fido2013, when you look at

Fido2013, when you look at the description of the ceiling function it states "Returns number rounded up, away from zero, to the nearest multiple of significance."

In this case as per the attached spreadsheet the working hours are 8:00 and the duration is 8:00 and the significance (i.e. limit) is 0:15. working hours - Duration = 0:00 so the result from the formula displays 0:15 as it is "rounding up away from zero" to the next multiple of 0:15.