Is it Possible?

[John]

Is it possible to 'round' a time to the next nearest 15min interval?

As an example in cell A1 I have a value that returns 2:07 PM (its formated
as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min
interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would
want to show 5:45 PM in B1 etc etc

Any guidance appreciated

[Franz Verga]

Nel post news:[email protected]
*John* ha scritto:

> Is it possible to 'round' a time to the next nearest 15min interval?
>
> As an example in cell A1 I have a value that returns 2:07 PM (its
> formated as h:mm AM/PM), but in B1 I wish to translate this to the
> nearest 15min interval in an hour which is 2:15 PM, if the value in
> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>
> Any guidance appreciated


Hi John,

maybe this can help:

=MROUND(E8,15/60/24)

but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of 2:15 PM,
maybe you can "play" with this formula to find the best result...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Franz Verga]

Nel post news:e%[email protected]
*Franz Verga* ha scritto:

> Nel post news:[email protected]
> *John* ha scritto:
>
>> Is it possible to 'round' a time to the next nearest 15min interval?
>>
>> As an example in cell A1 I have a value that returns 2:07 PM (its
>> formated as h:mm AM/PM), but in B1 I wish to translate this to the
>> nearest 15min interval in an hour which is 2:15 PM, if the value in
>> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>>
>> Any guidance appreciated
>
>
> Hi John,
>
> maybe this can help:
>
> =MROUND(E8,15/60/24)
>
> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of
> 2:15 PM, maybe you can "play" with this formula to find the best
> result...

I think I got it:

=MROUND(E8+30/60/60/24,15/60/24)

obviusly in E8 there is the time to round.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[John]

Franz

Thanks for your help, it certainly is nearly there and does round to the
nearest 15mins. But I'm looking to get the nearest 'next' 15min. Sounds
confusing but say if E8 showed 8:02 PM I'd want my formula to return 8:15 PM
and not 8:00 PM

But a smart formula you have given nonetheless

Thanks


"Franz Verga" <[email protected]> wrote in message
news:%[email protected]...
> Nel post news:e%[email protected]
> *Franz Verga* ha scritto:
>
>> Nel post news:[email protected]
>> *John* ha scritto:
>>
>>> Is it possible to 'round' a time to the next nearest 15min interval?
>>>
>>> As an example in cell A1 I have a value that returns 2:07 PM (its
>>> formated as h:mm AM/PM), but in B1 I wish to translate this to the
>>> nearest 15min interval in an hour which is 2:15 PM, if the value in
>>> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>>>
>>> Any guidance appreciated
>>
>>
>> Hi John,
>>
>> maybe this can help:
>>
>> =MROUND(E8,15/60/24)
>>
>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of
>> 2:15 PM, maybe you can "play" with this formula to find the best
>> result...
>
> I think I got it:
>
> =MROUND(E8+30/60/60/24,15/60/24)
>
> obviusly in E8 there is the time to round.
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

[Franz Verga]

Nel post news:[email protected]
*John* ha scritto:

I think finally I got it.

Try this one:

=IF(E8-MROUND(E8,15/60/24)>0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24))




> Franz
>
> Thanks for your help, it certainly is nearly there and does round to
> the nearest 15mins. But I'm looking to get the nearest 'next' 15min.
> Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to
> return 8:15 PM and not 8:00 PM
>
> But a smart formula you have given nonetheless
>
> Thanks
>
>
> "Franz Verga" <[email protected]> wrote in message
> news:%[email protected]...
>> Nel post news:e%[email protected]
>> *Franz Verga* ha scritto:
>>
>>> Nel post news:[email protected]
>>> *John* ha scritto:
>>>
>>>> Is it possible to 'round' a time to the next nearest 15min
>>>> interval? As an example in cell A1 I have a value that returns 2:07 PM
>>>> (its
>>>> formated as h:mm AM/PM), but in B1 I wish to translate this to the
>>>> nearest 15min interval in an hour which is 2:15 PM, if the value in
>>>> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>>>>
>>>> Any guidance appreciated
>>>
>>>
>>> Hi John,
>>>
>>> maybe this can help:
>>>
>>> =MROUND(E8,15/60/24)
>>>
>>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of
>>> 2:15 PM, maybe you can "play" with this formula to find the best
>>> result...
>>
>> I think I got it:
>>
>> =MROUND(E8+30/60/60/24,15/60/24)
>>
>> obviusly in E8 there is the time to round.
>>
>>
>> --
>> Hope I helped you.
>>
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[John]

Franz

Your an absolute genius. Many thanks for your determined efforts, its
exactly what I require

Thanks


"Franz Verga" <[email protected]> wrote in message
news:[email protected]...
> Nel post news:[email protected]
> *John* ha scritto:
>
> I think finally I got it.
>
> Try this one:
>
> =IF(E8-MROUND(E8,15/60/24)>0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24))
>
>
>
>
>> Franz
>>
>> Thanks for your help, it certainly is nearly there and does round to
>> the nearest 15mins. But I'm looking to get the nearest 'next' 15min.
>> Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to
>> return 8:15 PM and not 8:00 PM
>>
>> But a smart formula you have given nonetheless
>>
>> Thanks
>>
>>
>> "Franz Verga" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Nel post news:e%[email protected]
>>> *Franz Verga* ha scritto:
>>>
>>>> Nel post news:[email protected]
>>>> *John* ha scritto:
>>>>
>>>>> Is it possible to 'round' a time to the next nearest 15min
>>>>> interval? As an example in cell A1 I have a value that returns 2:07 PM
>>>>> (its
>>>>> formated as h:mm AM/PM), but in B1 I wish to translate this to the
>>>>> nearest 15min interval in an hour which is 2:15 PM, if the value in
>>>>> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>>>>>
>>>>> Any guidance appreciated
>>>>
>>>>
>>>> Hi John,
>>>>
>>>> maybe this can help:
>>>>
>>>> =MROUND(E8,15/60/24)
>>>>
>>>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of
>>>> 2:15 PM, maybe you can "play" with this formula to find the best
>>>> result...
>>>
>>> I think I got it:
>>>
>>> =MROUND(E8+30/60/60/24,15/60/24)
>>>
>>> obviusly in E8 there is the time to round.
>>>
>>>
>>> --
>>> Hope I helped you.
>>>
>>> Thanks in advance for your feedback.
>>>
>>> Ciao
>>>
>>> Franz Verga from Italy
>
> --
> (I'm not sure of names of menues, option and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

[John]

Franz

One small thing I've noticed is that when my time is exactly on the Qtr
hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is there a way
to leave these as 13:15?

Just a note if it is 15:31, then this should ound to 15:45 - as it does with
your existing formula

I just can't tweak your formula to the correct syntax

Thanks


"Franz Verga" <[email protected]> wrote in message
news:[email protected]...
> Nel post news:[email protected]
> *John* ha scritto:
>
> I think finally I got it.
>
> Try this one:
>
> =IF(E8-MROUND(E8,15/60/24)>0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24))
>
>
>
>
>> Franz
>>
>> Thanks for your help, it certainly is nearly there and does round to
>> the nearest 15mins. But I'm looking to get the nearest 'next' 15min.
>> Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to
>> return 8:15 PM and not 8:00 PM
>>
>> But a smart formula you have given nonetheless
>>
>> Thanks
>>
>>
>> "Franz Verga" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Nel post news:e%[email protected]
>>> *Franz Verga* ha scritto:
>>>
>>>> Nel post news:[email protected]
>>>> *John* ha scritto:
>>>>
>>>>> Is it possible to 'round' a time to the next nearest 15min
>>>>> interval? As an example in cell A1 I have a value that returns 2:07 PM
>>>>> (its
>>>>> formated as h:mm AM/PM), but in B1 I wish to translate this to the
>>>>> nearest 15min interval in an hour which is 2:15 PM, if the value in
>>>>> A1 was 5:39 PM I would want to show 5:45 PM in B1 etc etc
>>>>>
>>>>> Any guidance appreciated
>>>>
>>>>
>>>> Hi John,
>>>>
>>>> maybe this can help:
>>>>
>>>> =MROUND(E8,15/60/24)
>>>>
>>>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead of
>>>> 2:15 PM, maybe you can "play" with this formula to find the best
>>>> result...
>>>
>>> I think I got it:
>>>
>>> =MROUND(E8+30/60/60/24,15/60/24)
>>>
>>> obviusly in E8 there is the time to round.
>>>
>>>
>>> --
>>> Hope I helped you.
>>>
>>> Thanks in advance for your feedback.
>>>
>>> Ciao
>>>
>>> Franz Verga from Italy
>
> --
> (I'm not sure of names of menues, option and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

[Franz Verga]

Hi John,

try with this new formula:

=IF((A45+0.5/60/24)-(ROUND(A45/(15/60/24),1/60/24)*15/60/24)>0,(ROUND(A45/(15/60/24),1/60/24)*15/60/24)+15/60/24,(ROUND(A45/(15/60/24),1/60/24)*15/60/24))

I replaced MROUND with ROUND for two reasons: ROUND is more flexible then
MROUND and also becasuse ROUND is an Excel standard function, while MROUND
is an ATP one.

I plaied a little bit with the formula above and thikshold solve your
problem.


Nel post news:[email protected]
*John* ha scritto:

> Franz
>
> One small thing I've noticed is that when my time is exactly on the
> Qtr hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is
> there a way to leave these as 13:15?
>
> Just a note if it is 15:31, then this should ound to 15:45 - as it
> does with your existing formula
>
> I just can't tweak your formula to the correct syntax
>
> Thanks
>
>
> "Franz Verga" <[email protected]> wrote in message
> news:[email protected]...
>> Nel post news:[email protected]
>> *John* ha scritto:
>>
>> I think finally I got it.
>>
>> Try this one:
>>
>> =IF(E8-MROUND(E8,15/60/24)>0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24))
>>
>>
>>
>>
>>> Franz
>>>
>>> Thanks for your help, it certainly is nearly there and does round to
>>> the nearest 15mins. But I'm looking to get the nearest 'next' 15min.
>>> Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to
>>> return 8:15 PM and not 8:00 PM
>>>
>>> But a smart formula you have given nonetheless
>>>
>>> Thanks
>>>
>>>
>>> "Franz Verga" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> Nel post news:e%[email protected]
>>>> *Franz Verga* ha scritto:
>>>>
>>>>> Nel post news:[email protected]
>>>>> *John* ha scritto:
>>>>>
>>>>>> Is it possible to 'round' a time to the next nearest 15min
>>>>>> interval? As an example in cell A1 I have a value that returns
>>>>>> 2:07 PM (its
>>>>>> formated as h:mm AM/PM), but in B1 I wish to translate this to
>>>>>> the nearest 15min interval in an hour which is 2:15 PM, if the
>>>>>> value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc
>>>>>> etc Any guidance appreciated
>>>>>
>>>>>
>>>>> Hi John,
>>>>>
>>>>> maybe this can help:
>>>>>
>>>>> =MROUND(E8,15/60/24)
>>>>>
>>>>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead
>>>>> of 2:15 PM, maybe you can "play" with this formula to find the
>>>>> best result...
>>>>
>>>> I think I got it:
>>>>
>>>> =MROUND(E8+30/60/60/24,15/60/24)
>>>>
>>>> obviusly in E8 there is the time to round.
>>>>
>>>>
>>>> --
>>>> Hope I helped you.
>>>>
>>>> Thanks in advance for your feedback.
>>>>
>>>> Ciao
>>>>
>>>> Franz Verga from Italy
>>
>> --
>> (I'm not sure of names of menues, option and commands, because
>> translating from the Italian version of Excel...)
>>
>> Hope I helped you.
>>
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[daddylonglegs]

try with this new formula:

=IF((A45+0.5/60/24)-(ROUND(A45/(15/60/24),1/60/24)*15/60/24)>0,(ROUND(A45/(15/60/24),1/60/24)*15/60/24)+15/60/24,(ROUND(A45/(15/60/24),1/60/24)*15/60/24))

As somebody said of one of my recent postings here, "this is like using a sledgehammer to kill an ant".

To round up a time in A1 to the next 15 minute increment

=CEILING(A1,"00:15")

format as time

[Franz Verga]

I 've gotted help from the Italian ng (Grazie VeroToad)...

try this:

=TIME(HOUR(A1);15*ROUNDUP((MINUTE(A1)+1)/15;0);0)


Nel post news:[email protected]
*John* ha scritto:

> Franz
>
> One small thing I've noticed is that when my time is exactly on the
> Qtr hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is
> there a way to leave these as 13:15?
>
> Just a note if it is 15:31, then this should ound to 15:45 - as it
> does with your existing formula
>
> I just can't tweak your formula to the correct syntax
>
> Thanks
>
>
> "Franz Verga" <[email protected]> wrote in message
> news:[email protected]...
>> Nel post news:[email protected]
>> *John* ha scritto:
>>
>> I think finally I got it.
>>
>> Try this one:
>>
>> =IF(E8-MROUND(E8,15/60/24)>0,MROUND(E8,15/60/24)+15/60/24,MROUND(E8,15/60/24))
>>
>>
>>
>>
>>> Franz
>>>
>>> Thanks for your help, it certainly is nearly there and does round to
>>> the nearest 15mins. But I'm looking to get the nearest 'next' 15min.
>>> Sounds confusing but say if E8 showed 8:02 PM I'd want my formula to
>>> return 8:15 PM and not 8:00 PM
>>>
>>> But a smart formula you have given nonetheless
>>>
>>> Thanks
>>>
>>>
>>> "Franz Verga" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> Nel post news:e%[email protected]
>>>> *Franz Verga* ha scritto:
>>>>
>>>>> Nel post news:[email protected]
>>>>> *John* ha scritto:
>>>>>
>>>>>> Is it possible to 'round' a time to the next nearest 15min
>>>>>> interval? As an example in cell A1 I have a value that returns
>>>>>> 2:07 PM (its
>>>>>> formated as h:mm AM/PM), but in B1 I wish to translate this to
>>>>>> the nearest 15min interval in an hour which is 2:15 PM, if the
>>>>>> value in A1 was 5:39 PM I would want to show 5:45 PM in B1 etc
>>>>>> etc Any guidance appreciated
>>>>>
>>>>>
>>>>> Hi John,
>>>>>
>>>>> maybe this can help:
>>>>>
>>>>> =MROUND(E8,15/60/24)
>>>>>
>>>>> but for 2:07 PM the nearest 15 minutes take to 2:00 PM, instead
>>>>> of 2:15 PM, maybe you can "play" with this formula to find the
>>>>> best result...
>>>>
>>>> I think I got it:
>>>>
>>>> =MROUND(E8+30/60/60/24,15/60/24)
>>>>
>>>> obviusly in E8 there is the time to round.
>>>>
>>>>
>>>> --
>>>> Hope I helped you.
>>>>
>>>> Thanks in advance for your feedback.
>>>>
>>>> Ciao
>>>>
>>>> Franz Verga from Italy
>>
>> --
>> (I'm not sure of names of menues, option and commands, because
>> translating from the Italian version of Excel...)
>>
>> Hope I helped you.
>>
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Franz Verga]

daddylonglegs wrote:
> Franz Verga Wrote:
>> try with this new formula:
>>
>> =IF((A45+0.5/60/24)-(ROUND(A45/(15/60/24),1/60/24)*15/60/24)>0,(ROUND(A45/(15/60/24),1/60/24)*15/60/24)+15/60/24,(ROUND(A45/(15/60/24),1/60/24)*15/60/24))
>>
>
> As somebody said of one of my recent postings here, "this is like
> using a sledgehammer to kill an ant".

Yes, you're right, but for me this is the first time "playing" with time...

> To round up a time in A1 to the next 15 minute increment
>
> =CEILING(A1,"00:15")
>
> format as time

But in this way yo rouund 13:15 to 13:15, while the OP requested to round
13:15 to 13:30 and so on, so I think the smaller formula should be:

=CEILING(A1+"0.01","0.15")

--
Ciao

Franz Verga from Italy

[daddylonglegs]

Hi Frank

I think my suggestion does what the poster required, see here...

One small thing I've noticed is that when my time is exactly on the Qtr
hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is there a way
to leave these as 13:15?

Just a note if it is 15:31, then this should ound to 15:45 - as it does with
your existing formula

[Franz Verga]

daddylonglegs wrote:
> Hi Frank

My name is Franz... :-)

> I think my suggestion does what the poster required, see here...
>
> John Wrote:
>>
>> One small thing I've noticed is that when my time is exactly on the
>> Qtr
>> hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is there
>> a way
>> to leave these as 13:15?
>>
>> Just a note if it is 15:31, then this should ound to 15:45 - as it
>> does with
>> your existing formula


Sorry, you're right, but when I read the post this morning, I think I was
still sleeping, so I read it in the way the OP wanted to round 13:15 to
13:30... :-)


--
Ciao

Franz Verga from Italy

[John]

Thanks everyone I suppose the trick is to know that all these functions
exist!




"Franz Verga" <[email protected]> wrote in message
news:[email protected]...
> daddylonglegs wrote:
>> Hi Frank
>
> My name is Franz... :-)
>
>> I think my suggestion does what the poster required, see here...
>>
>> John Wrote:
>>>
>>> One small thing I've noticed is that when my time is exactly on the
>>> Qtr
>>> hour, say 13:15 this also rounds to the next 15 i.e. 13:30. Is there
>>> a way
>>> to leave these as 13:15?
>>>
>>> Just a note if it is 15:31, then this should ound to 15:45 - as it
>>> does with
>>> your existing formula
>
>
> Sorry, you're right, but when I read the post this morning, I think I was
> still sleeping, so I read it in the way the OP wanted to round 13:15 to
> 13:30... :-)
>
>
> --
> Ciao
>
> Franz Verga from Italy
>