Rounding times to next 15 minutes

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=CEILING(B2,TIME(0,15,0))
 

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=MROUND(B2,TIME(0,15,0))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
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 -
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,442
Office Version
  1. 365
Platform
  1. Windows
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,297
Messages
5,853,131
Members
431,549
Latest member
NnAa

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
Top