A simple formula question: Return next quarter of an hour

[Jaymond Flurrie]

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)

 

[Ron Coderre]

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]

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!

 

[Ron Coderre]

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]

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]

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.