How do I round off times in Excel?

[Andrew]

I am calculating times by dividing a distance by a speed. The results are in
cells formatted hh:mm but always seem to be rounded down so that the sum of
the column may be several minutes out, compared to the times displayed.

If I reformat the cells hh:mm:ss then I can see for example what was 01:32
displayed as 01:32:57. I want this to appear as 01:33.

Any ideas?

[VBA Noob]

Try this

=ROUND(A18*1440,0)/1440

Rounds down if 29 sec and rounds up 31 sec

VBA Noob

[Biff]

Hi!

So you want to round to the nearest minute?

=ROUND(A1/(1/1440),0)*1/1440

Or:

=ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0)

Biff

"Andrew" <[email protected]> wrote in message
news:[email protected]...
>I am calculating times by dividing a distance by a speed. The results are
>in
> cells formatted hh:mm but always seem to be rounded down so that the sum
> of
> the column may be several minutes out, compared to the times displayed.
>
> If I reformat the cells hh:mm:ss then I can see for example what was 01:32
> displayed as 01:32:57. I want this to appear as 01:33.
>
> Any ideas?

[Andrew]

Thanks very much

"Biff" wrote:

> Hi!
>
> So you want to round to the nearest minute?
>
> =ROUND(A1/(1/1440),0)*1/1440
>
> Or:
>
> =ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0)
>
> Biff
>
> "Andrew" <[email protected]> wrote in message
> news:[email protected]...
> >I am calculating times by dividing a distance by a speed. The results are
> >in
> > cells formatted hh:mm but always seem to be rounded down so that the sum
> > of
> > the column may be several minutes out, compared to the times displayed.
> >
> > If I reformat the cells hh:mm:ss then I can see for example what was 01:32
> > displayed as 01:32:57. I want this to appear as 01:33.
> >
> > Any ideas?
>
>
>

[Andrew]

Thanks very much

"VBA Noob" wrote:

>
> Try this
>
> =ROUND(A18*1440,0)/1440
>
> Rounds down if 29 sec and rounds up 31 sec
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=562623
>
>

[Biff]

You're welcome!

Biff

"Andrew" <[email protected]> wrote in message
news:[email protected]...
> Thanks very much
>
> "Biff" wrote:
>
>> Hi!
>>
>> So you want to round to the nearest minute?
>>
>> =ROUND(A1/(1/1440),0)*1/1440
>>
>> Or:
>>
>> =ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0)
>>
>> Biff
>>
>> "Andrew" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am calculating times by dividing a distance by a speed. The results
>> >are
>> >in
>> > cells formatted hh:mm but always seem to be rounded down so that the
>> > sum
>> > of
>> > the column may be several minutes out, compared to the times displayed.
>> >
>> > If I reformat the cells hh:mm:ss then I can see for example what was
>> > 01:32
>> > displayed as 01:32:57. I want this to appear as 01:33.
>> >
>> > Any ideas?
>>
>>
>>