Issue with =CEILING()

[JimR2]

I’ve created a spreadsheet for hours worked using my employers parameters.

if mm greater than 0 but = to or less than 15 then mm = 15
if mm greater than 15 but = to or less than 30 then mm = 30
if mm greater than 30 but = to or less than 45 then mm = 45
if mm greater than 45 but = to or less than 60 then mm = 60
Here it is

I’ve formatted column B+C as HH:MM AM/PM, D+G as HH:MM, F as a number: #.00

Function for C is =SUM(C2-B2), F is =(ROUND(G2*24,2)), G is =CEILING(D2,“0:15”).

Everything seems to work except line 8, line 7 is the same but it is AM not PM and it works as expected.
Why does it round up to 01:15 when it should be 01:00?

Column E is a wildcard I haven’t tackled yet.

[karolus]

Hello

The rounding error occurs from Column D

=CEILING(MROUND(D2;1/1440)*24;0.25)

[Villeroy]

Formatting makes no difference at all. Bad formatting can make correct values look wrong and wrong values look right.

@JimR2

if mm greater than 0 but = to or less than 15 then mm = 15
if mm greater than 15 but = to or less than 30 then mm = 30
if mm greater than 30 but = to or less than 45 then mm = 45
if mm greater than 45 but = to or less than 60 then mm = 60

The LOOKUP function handles this.
Or =FLOOR(A1-1;15) for integer numbers >0

[PKG]

This is your formula

=ROUNDUP(D2*(24*4);0)/(24*4)*24

ROUNDUP TIME.ods (114,2 KB)

[karolus]

And this is the short:

    =CEILING(D2*24;0.25)

[JimR2]

karolus,
This worked except for line 8. It returns the value of 1.25 when it should be 1.0.
It’s odd that it only seems to be 13:00 to 14:00 that returns an incorrect value.

[JimR2]

Thanks PKG,
This resolved the issue I was having with line 8. 13:00 to 14:00

[karolus]

Both Functions returns here the same (wrong) result for D8: 1.25

[JimR2]

karolus,
I can’t get =CEILING(D224;0.25) to work correctly for 13:00 to 14:00. I tried using it in column F and G and still get 1.25 instead of 1.0. =ROUNDUP(D2*(24*4);0)/(24*4)*24 worked well and like your solution it eliminates the need for hidden column G. What I don’t understand is why =CEILING(D224;0.25) works for everything I’ve tried but 13:00 to 14:00. Why they both return 1.25 for you and not me. Could it be OS or Libre Office version that’s making the difference?

[karolus]

Dont know why theris a difference, but it seems =CEILING(MROUND(D2;1/1440)*24;0.25) solve it also.
(it rounds first D? to Minutes)

[karolus]

Floating-point_arithmetic

what Calc stores:

in the right Column there are the Differences formatted with 18digit after period.
01:00:00	02:00:00	0,041666666666666700
02:00:00	03:00:00	0,041666666666666700
03:00:00	04:00:00	0,041666666666666700
04:00:00	05:00:00	0,041666666666666700
05:00:00	06:00:00	0,041666666666666700
06:00:00	07:00:00	0,041666666666666700
07:00:00	08:00:00	0,041666666666666600
08:00:00	09:00:00	0,041666666666666700
09:00:00	10:00:00	0,041666666666666700
10:00:00	11:00:00	0,041666666666666700
11:00:00	12:00:00	0,041666666666666800  ###
12:00:00	13:00:00	0,041666666666666700
13:00:00	14:00:00	0,041666666666666700
14:00:00	15:00:00	0,041666666666666900 ###
15:00:00	16:00:00	0,041666666666666900
16:00:00	17:00:00	0,041666666666666900
17:00:00	18:00:00	0,041666666666666900
18:00:00	19:00:00	0,041666666666666900
19:00:00	20:00:00	0,041666666666666900
20:00:00	21:00:00	0,041666666666666900
21:00:00	22:00:00	0,041666666666667000  ## !!
22:00:00	23:00:00	0,041666666666666900
23:00:00	24:00:00	0,041666666666666700
24:00:00	25:00:00	0,041666666666666900
25:00:00	26:00:00	0,041666666666666500