Rounding times to next 15 minutes

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a column with dates/times. The column length is variable. The format looks like "24/01/2022 06:30:00".

I need to go through the column and round up to the *next* 15 minute slot.

For example, "24/01/2022 06:33:00" would become "24/01/2022 06:45:00".

Thanks all!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
How about
Excel Formula:
=CEILING(B2,TIME(0,15,0))
 
Upvote 0
Tried both of these and they work nicely - unfortunately I've created a fresh set of problems lol.

I've had a look at CEILING vs MROUND and other rounding operations - is there a way to get this rounding to the closest 15 minute slot instead of the next 15 minute slot?

I tried replacing ceiling with mround but I believe I've not specified the desired integer correctly
 
Upvote 0
How about
Excel Formula:
=MROUND(B2,TIME(0,15,0))
 
Upvote 0
Hi,

Or this:

Book3.xlsx
AB
11/24/22 6:451/24/22 6:45
21/24/22 6:481/24/22 6:45
31/24/22 6:511/24/22 6:45
41/24/22 6:541/24/22 7:00
51/24/22 6:571/24/22 7:00
61/24/22 7:001/24/22 7:00
71/24/22 7:031/24/22 7:00
81/24/22 7:061/24/22 7:00
91/24/22 7:091/24/22 7:15
101/24/22 7:121/24/22 7:15
111/24/22 7:151/24/22 7:15
121/24/22 7:181/24/22 7:15
131/24/22 7:211/24/22 7:15
141/24/22 7:241/24/22 7:30
151/24/22 7:271/24/22 7:30
161/24/22 7:301/24/22 7:30
171/24/22 7:331/24/22 7:30
181/24/22 7:361/24/22 7:30
191/24/22 7:391/24/22 7:45
201/24/22 7:421/24/22 7:45
Sheet959
Cell Formulas
RangeFormula
B1:B20B1=MROUND(A1,1/96)
 
Upvote 0
How about
Excel Formula:
=CEILING(B2,TIME(0,15,0))
Ah, okay so that was more obvious than I thought...

I'm trying to change the formula so its a dynamic array - it worked fine for the CEILING.MATH but MROUND isn't playing nicely

for CEILING.MATH I tried using
Excel Formula:
=CEILING.MATH(A1:INDEX(A:A,COUNTA(A:A)),TIME(0,15,0)
which works, but swapping that out for MROUND returns VALUE?

I'm guessing my method of array is kind of poor anyway
 
Upvote 0
Ah, okay so that was more obvious than I thought...

I'm trying to change the formula so its a dynamic array - it worked fine for the CEILING.MATH but MROUND isn't playing nicely

for CEILING.MATH I tried using
Excel Formula:
=CEILING.MATH(A1:INDEX(A:A,COUNTA(A:A)),TIME(0,15,0)
which works, but swapping that out for MROUND returns VALUE?

I'm guessing my method of array is kind of poor anyway
quoted the wrong thing there I reckon, my bad -
 
Upvote 0
Mround does not like arrays, however you could use something like
Excel Formula:
=CEILING(A1:INDEX(A:A,COUNTA(A:A))-TIME(0,7,30),TIME(0,15,0))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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