Rounding using ceiling with conditions

[ICKIER]

I'm trying to round to the nearest quarter hour but if the rounding is less than 1:15 I want the cell to just display 1:15

I've tried =IF(CEILING((B2-A2),"00:15")<"01:15","1:15",CEILING((B2-A2),"00:15")) and it just puts 1:15 is every cell no matter if it's above or below 1:15

Below is an example. I only want the second column of time to be displayed. I'm displaying the first column of time for reference of the actual time.
The rounding is correct except for I do not want 1:15 to round up to 1:30.

Any help would be appreciated.

[TABLE="width: 395"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]3/1/2019 10:40[/TD]
[TD="align: right"]3/1/2019 11:50[/TD]
[TD="align: right"]1:10[/TD]
[TD="align: right"]1:15[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019 3:30[/TD]
[TD="align: right"]3/1/2019 5:20[/TD]
[TD="align: right"]1:50[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019 13:05[/TD]
[TD="align: right"]3/1/2019 13:30[/TD]
[TD="align: right"]0:25[/TD]
[TD="align: right"]1:15[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2019 7:40[/TD]
[TD="align: right"]3/2/2019 8:55[/TD]
[TD="align: right"]1:15[/TD]
[TD="align: right"]1:30[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Try this,

=MAX(0.0520833333333333,CEILING(B2-A2,0.0104166666666667))

0.0104166666666667 is 15 minutes, 0.0520833333333333 is 1 hour 15 minutes

 

[Scott Huish]

=max("1:15"+0,ceiling(b2-a2,"00:15"+0))

 

[ICKIER]

=max("1:15"+0,ceiling(b2-a2,"00:15"+0))

Still rounds 1:15 to 1:30

 

[ICKIER]

Try this,

=MAX(0.0520833333333333,CEILING(B2-A2,0.0104166666666667))

0.0104166666666667 is 15 minutes, 0.0520833333333333 is 1 hour 15 minutes

This rounded to 2:00

 

[mrshl9898]

Just to check, that's for the first row, which is row 2?

 

[joeu2004]

[TABLE="width: 395"]
<tbody>[TR]
[TD="align: right"]3/1/2019 10:40[/TD]
[TD="align: right"]3/1/2019 11:50[/TD]
[TD="align: right"]1:10[/TD]
[TD="align: right"]1:15[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019 3:30[/TD]
[TD="align: right"]3/1/2019 5:20[/TD]
[TD="align: right"]1:50[/TD]
[TD="align: right"]2:00[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019 13:05[/TD]
[TD="align: right"]3/1/2019 13:30[/TD]
[TD="align: right"]0:25[/TD]
[TD="align: right"]1:15[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2019 7:40[/TD]
[TD="align: right"]3/2/2019 8:55[/TD]
[TD="align: right"]1:15[/TD]
[TD="align: right"]1:30[/TD]
[/TR]
</tbody>[/TABLE]

It is sufficient to write =MAX("1:15", CEILING(TEXT(B2-A2,"[h]:mm"), "0:15"))

But if you have doubts about using undocumented features, you can write:

=MAX(TIME(0,1,15), CEILING(--TEXT(B2-A2,"[h]:mm"), TIME(0,0,15)))

Never replace time representations with decimal "equivalents". The internal binary representations are different; and sometimes, that difference matters.

MAX("1:15", CEILING(B5-A5,"0:15")) returns 1:30 because B5-A5 is greater than the binary representation of 1:15, despite appearances.

In decimal, B5-A5 is 0.0520833333357587, whereas 1:15 is 0.0520833333333333. The difference arises because of anomalies with the 64-bit binary floating-point representation of time.

The formula above rounds B5-A5 to the correct binary representation of time rounded to the minute.

Caveat: I usually deprecate the use of most decimal fractions (including Excel time) in the second parameter of CEILING, again because of anomalies of the binary representation. The CEILING result might not exactly match the binary representation of the apparent time. The infinitesimal difference might cause problems when using MATCH and other look-up functions.

 

[joeu2004]

=IF(CEILING((B2-A2),"00:15")<"01:15","1:15",CEILING((B2-A2),"00:15"))

Although it is not relevant to the solution to your problem, it might be helpful to know that normally, we should not write time as quoted strings alone. They should be converted to their numeric representation in some way.

Although we can get away with it in some function parameters (e.g. CEILING), the highlighted references are used as strings, not numbers. So CEILING(...)<"1:15" is always true because in Excel, all text is treated as greater than any number. And in that case, IF() returns the string "1:15", not numeric Excel time.

You could write --"1:15". The double-negate (any arithmetic operation; some people like +0 or *1) converts text that Excel recognizes as a number to its numeric representation. For time, you can also write TIME(0,1,15), which might be better if the file is shared by users in other regions.

(Off-hand, I don't know of any country that does not use colon to separate parts of time. But I believe I did encounter that situation once.)

 

[joeu2004]

Errata.... Of course, I was reading 1:15 and 0:15 as m:ss instead of h:mm. Mea culpa!
The equivalent TIME expressions are TIME(1,15,0) and TIME(0,15,0).
Perhaps a good argument for writing --"1:15" and --"0:15" instead. (wink)

 

[Scott Huish]

Try this:
=MAX("1:15"+0,CEILING(ROUND(B2-A2,2),"00:15"+0))