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?

Round hh:mm:ss to whole hh:mm
 

Try

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

format as [h]:mm


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513450

Round hh:mm:ss to whole hh: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" wrote in message
...
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?

Round hh:mm:ss to whole hh:mm
 

On Thu, 16 Feb 2006 16:52:27 -0800, PJH 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