Round hh:mm:ss to whole hh:mm

[PJH]

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?

 

[daddylonglegs]

Try

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

format as [h]:mm

 

[Peo Sjoblom]

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

 

[Ron Rosenfeld]

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