Rounding to nearest quarter hour?

If we have a column of times we're adding up in a formula like, say,
=sum(C4:C20) and we're dealing with times is there a way to modify
that formula so that it rounds the total to the nearest quarter hour?

p.s., since the spreadsheet had to take into account "negative" hours
in terms of subtracting hours, the spreadsheet is set to the 1904
system. Don't know if that makes any difference to the above.

Tx.

Hi,

Try this:

=TIME(HOUR(SUM(C4:C20)),ROUND(MINUTE(SUM(C4:C20))/15,0)*15,0)

or this:

=ROUND(SUM(C4:C20)/0.0104166666666667,0)*0.0104166666666667

or this (you'll need the Analysis Toolpack installed for this one):

=MROUND(SUM(C4:C20),0.0104166666666667)

Regards,
KL


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> If we have a column of times we're adding up in a formula like, say,
> =sum(C4:C20) and we're dealing with times is there a way to modify
> that formula so that it rounds the total to the nearest quarter hour?
>
> p.s., since the spreadsheet had to take into account "negative" hours
> in terms of subtracting hours, the spreadsheet is set to the 1904
> system. Don't know if that makes any difference to the above.
>
> Tx.
>

See http://www.xldynamic.com/source/xld.Rounding.html#time

--
HTH

Bob Phillips

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
> If we have a column of times we're adding up in a formula like, say,
> =sum(C4:C20) and we're dealing with times is there a way to modify
> that formula so that it rounds the total to the nearest quarter hour?
>
> p.s., since the spreadsheet had to take into account "negative" hours
> in terms of subtracting hours, the spreadsheet is set to the 1904
> system. Don't know if that makes any difference to the above.
>
> Tx.
>

one way:

Since XL stores times as fractional days and quarter hours are 1/96 of a
day:

=ROUND(SUM(C4:C20)*96,0)/96

The date system makes no difference unless the SUM is negative.

In article <[email protected]>,
StargateFan <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

> If we have a column of times we're adding up in a formula like, say,
> =sum(C4:C20) and we're dealing with times is there a way to modify
> that formula so that it rounds the total to the nearest quarter hour?
>
> p.s., since the spreadsheet had to take into account "negative" hours
> in terms of subtracting hours, the spreadsheet is set to the 1904
> system. Don't know if that makes any difference to the above.
>
> Tx.

The first formula below seemed to work perfectly in the test runs, thanks!
I'll add it to my main overtime spreadsheet and give it a whirl.

It's easier now just to put the actual times and then let Excel round only
the last total figure rather than the reverse as we've been doing up till
now. By putting in the actual real minutes rather than rounding each day
and _then_ rounding in last figure, I believe that reflects the truer
overtime worked. Very kewl! :oD

**********************
"KL" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Try this:
>
> =TIME(HOUR(SUM(C4:C20)),ROUND(MINUTE(SUM(C4:C20))/15,0)*15,0)
>
> or this:
>
> =ROUND(SUM(C4:C20)/0.0104166666666667,0)*0.0104166666666667
>
> or this (you'll need the Analysis Toolpack installed for this one):
>
> =MROUND(SUM(C4:C20),0.0104166666666667)
>
> Regards,
> KL
>
>
> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> news:[email protected]...
> > If we have a column of times we're adding up in a formula like, say,
> > =sum(C4:C20) and we're dealing with times is there a way to modify
> > that formula so that it rounds the total to the nearest quarter hour?
> >
> > p.s., since the spreadsheet had to take into account "negative" hours
> > in terms of subtracting hours, the spreadsheet is set to the 1904
> > system. Don't know if that makes any difference to the above.
> >
> > Tx.
> >
>
>

Hi,

My personal prefernce is actiually with one that I didn't suggest:

=ROUND(SUM(C4:C20)*96,0)/96

Regards,
KL


"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
> The first formula below seemed to work perfectly in the test runs, thanks!
> I'll add it to my main overtime spreadsheet and give it a whirl.
>
> It's easier now just to put the actual times and then let Excel round only
> the last total figure rather than the reverse as we've been doing up till
> now. By putting in the actual real minutes rather than rounding each day
> and _then_ rounding in last figure, I believe that reflects the truer
> overtime worked. Very kewl! :oD
>
> **********************
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =TIME(HOUR(SUM(C4:C20)),ROUND(MINUTE(SUM(C4:C20))/15,0)*15,0)
>>
>> or this:
>>
>> =ROUND(SUM(C4:C20)/0.0104166666666667,0)*0.0104166666666667
>>
>> or this (you'll need the Analysis Toolpack installed for this one):
>>
>> =MROUND(SUM(C4:C20),0.0104166666666667)
>>
>> Regards,
>> KL
>>
>>
>> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>> news:[email protected]...
>> > If we have a column of times we're adding up in a formula like, say,
>> > =sum(C4:C20) and we're dealing with times is there a way to modify
>> > that formula so that it rounds the total to the nearest quarter hour?
>> >
>> > p.s., since the spreadsheet had to take into account "negative" hours
>> > in terms of subtracting hours, the spreadsheet is set to the 1904
>> > system. Don't know if that makes any difference to the above.
>> >
>> > Tx.
>> >
>>
>>
>
>