A simple formula question: Return next quarter of an hour

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
For a change I ask something simple:

I want a formula that returns for me the next quarter of an hour. So if the clock is now 4:29:29, I want the return value to be 4:30:00 and if the clock is 12:19:43, I want the return value to be 12:30:00. it doesn't matter which way it treats the exact moments (if time is 12:00:00, return either 12:00:00 or 12:15:00, doesn't matter).

My solution is this and it seems to work:
=ROUNDDOWN(MOD(NOW();1)*24;0)&":"&IF(MOD(MOD(NOW();1)*24;1)>0,75;1;IF(MOD(MOD(NOW();1)*24;1)>0,5;0,75;IF(MOD(MOD(NOW();1)*24;1)>0,25;0,5;0,25)))*60&":00"

but do you have something simpler? (I use semicolon as decimal separator instead of comma, so don't let that one fool you)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Try this regular formula:
=CEILING(A1,TIME(0,15,0))

Format that cell as time.
or...maybe this will work for you:
=TEXT(CEILING(NOW(),TIME(0,15,0)),"h:mm:ss")

Note: The NOW function won't recalc without prompting of some kind. Pressing F9, entering/changing a value, etc.

Is that something you can work with?
 

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
Try this regular formula:
=CEILING(A1,TIME(0,15,0))

Format that cell as time.
or...maybe this will work for you:
=TEXT(CEILING(NOW(),TIME(0,15,0)),"h:mm:ss")

Note: The NOW function won't recalc without prompting of some kind. Pressing F9, entering/changing a value, etc.

Is that something you can work with?

Yeah, that works much better. The problem with that my formula (although I think it's very easy to fix) is that it returns times like 4:60:00 when it should return 5:00:00.

Thanks for the answer!

edit. I fixed it for my use to be =MOD(CEILING(NOW();TIME(0;15;0));1) the difference is that your formula used days there too (which obviously is useful in a lot of cases).

At least I learnt a now function, CEILING!
 
Last edited:

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
I'm not seeing that.

Here's my test:
A1: 4:46:00 AM
B1: =TEXT(CEILING(A1,TIME(0,15,0)),"h:mm:ss")

Returned value of B1: 5:00:00

What am I missing?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
For a change I ask something simple:

I want a formula that returns for me the next quarter of an hour. So if the clock is now 4:29:29, I want the return value to be 4:30:00 and if the clock is 12:19:43, I want the return value to be 12:30:00. it doesn't matter which way it treats the exact moments (if time is 12:00:00, return either 12:00:00 or 12:15:00, doesn't matter).

My solution is this and it seems to work:
=ROUNDDOWN(MOD(NOW();1)*24;0)&":"&IF(MOD(MOD(NOW();1)*24;1)>0,75;1;IF(MOD(MOD(NOW();1)*24;1)>0,5;0,75;IF(MOD(MOD(NOW();1)*24;1)>0,25;0,5;0,25)))*60&":00"

but do you have something simpler? (I use semicolon as decimal separator instead of comma, so don't let that one fool you)

Perhaps:

=ROUNDUP(MOD(NOW(),1)*24/0.25,0)*(0.25/24)

Does this give the expected outcomes?
 

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
I'm not seeing that.

Here's my test:
A1: 4:46:00 AM
B1: =TEXT(CEILING(A1,TIME(0,15,0)),"h:mm:ss")

Returned value of B1: 5:00:00

What am I missing?

I meant my formula had that error. The only "flaw" in your formula was that it used dates as well, meaning that when I have a time like 5:00:00 and take 4:00:00 out of that, the answer was way too big because of the dates. But like said, it doesn't mean that your formula is wrong, I just used it the wrong way.

edit. Nevermind, this version seems to work as is.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

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