Rounding time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amy - 12 Sep 2007 07:38 GMT

Hi,

I would like to round time to the nearest minute in Excel. For example have
a time that is 7:03:01 and 7:03:59 and they both appear as 7:03 with the
format h:mm when I would prefer they appear as 7:03 and 7:04. Can anyone
please tell me how I can do this.

Regards,

Amy

Reply to this Message

Mike H - 12 Sep 2007 08:36 GMT

Hi Amy,

This rounds the time in A1 to the nearest minute.

=ROUND((A1)/"0:01:00",0)*"0:01:00"

Mike

> Hi,
>
[quoted text clipped - 6 lines]
>
> Amy

Reply to this Message

JE McGimpsey - 12 Sep 2007 08:38 GMT

One way:

Since XL stores times as fractional days, 1 = 24 hours * 60 minutes =
1440 minutes. So,

=ROUND(A1*1440,0)/1440

Format as time, if necessary.

> Hi,
>
[quoted text clipped - 6 lines]
>
> Amy

Reply to this Message

David Biddulph - 12 Sep 2007 08:48 GMT

=MROUND(A28,1/1440)

Signature

David Biddulph

> Hi,
>
[quoted text clipped - 7 lines]
>
> Amy

Reply to this Message

Rick Rothstein (MVP - VB) - 12 Sep 2007 08:55 GMT

In addition to the posted ROUND solutions, if you have the Analysis ToolPak
add-in added in...

=MROUND(F1,TIME(0,1,0))

appears to also work.

Rick

> Hi,
>
[quoted text clipped - 7 lines]
>
> Amy

Reply to this Message