Rounding using ceiling with conditions

ICKIER

New Member
Joined
Mar 11, 2019
Messages
7
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.

3/1/2019 10:403/1/2019 11:501:101:15
3/1/2019 3:303/1/2019 5:201:502:00
3/1/2019 13:053/1/2019 13:300:251:15
3/2/2019 7:403/2/2019 8:551:151:30

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try this,

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

0.0104166666666667 is 15 minutes, 0.0520833333333333 is 1 hour 15 minutes
 
Last edited:
Upvote 0
3/1/2019 10:403/1/2019 11:501:101:15
3/1/2019 3:303/1/2019 5:201:502:00
3/1/2019 13:053/1/2019 13:300:251:15
3/2/2019 7:403/2/2019 8:551:151:30

<tbody>
</tbody>

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.
 
Last edited:
Upvote 0
=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.)
 
Upvote 0
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top