Round hh:mm:ss to whole hh:mm

I am using the following:

=TEXT((J30/$B$31)/24, "h:mm")

to convert hours and hundreths to hours and minutes. Excel is dropping the
seconds and not rounding up. (e.g., 9:00:36 should round to 9:01)

Any ideas?

Try

=ROUND((J30/$B$31)*6,1)/144

format as [h]:mm

Then you need to use round, btw is there any particular reason you are using
TEXT?


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

will round the time value in A1 to nearest minute
could look like

=ROUND((J30/$B$31/24)/TIME(,1,),0)*TIME(,1,)

of course that can be wrapped in TEXT

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"PJH" <PJH@discussions.microsoft.com> wrote in message
news:2A802917-69F6-4C67-99CF-30845C224C76@microsoft.com...
>I am using the following:
>
> =TEXT((J30/$B$31)/24, "h:mm")
>
> to convert hours and hundreths to hours and minutes. Excel is dropping
> the
> seconds and not rounding up. (e.g., 9:00:36 should round to 9:01)
>
> Any ideas?

On Thu, 16 Feb 2006 16:52:27 -0800, PJH <PJH@discussions.microsoft.com> wrote:

>I am using the following:
>
>=TEXT((J30/$B$31)/24, "h:mm")
>
>to convert hours and hundreths to hours and minutes. Excel is dropping the
>seconds and not rounding up. (e.g., 9:00:36 should round to 9:01)
>
>Any ideas?


You need to round your result to the nearest minute:


=TEXT(ROUND((J30/$B$31)/24/TIME(0,1,0),0)*TIME(0,1,0), "h:mm")


--ron