Need excel formula to display 28.50hrs in HRS & MINS?
 

I am trying to calculate distance, speed and time to cover the distance in
Excel spreadsheet.
I am using distance/speed to display the time in hours & decimals of an
hour, e.g. 28.50hrs [which is 28 hrs 30mins]; but can't come up with a
consistent formula to display the time in hours and mins. =TEXT (F2/24 etc.)
does not work once the number of hours is more than 24hrs. Can anyone
suggest a formula please?
--
Regards
rbc

On Wed, 5 Jan 2005 05:01:02 -0800, rbc wrote:

I am trying to calculate distance, speed and time to cover the distance in
Excel spreadsheet.
I am using distance/speed to display the time in hours & decimals of an
hour, e.g. 28.50hrs [which is 28 hrs 30mins]; but can't come up with a
consistent formula to display the time in hours and mins. =TEXT (F2/24 etc.)
does not work once the number of hours is more than 24hrs. Can anyone
suggest a formula please?

=TEXT(F2/24,"[h]:mm")

The brackets around the 'h' allow it to display more than 24 hours.


--ron

rbc,

=TEXT(F2/24,"[h]:mm")

or, more simply:

=F2/24

then format for custom [h]:mm

HTH,
Bernie
MS Excel MVP

"rbc" wrote in message
...
I am trying to calculate distance, speed and time to cover the distance in
Excel spreadsheet.
I am using distance/speed to display the time in hours & decimals of an
hour, e.g. 28.50hrs [which is 28 hrs 30mins]; but can't come up with a
consistent formula to display the time in hours and mins. =TEXT (F2/24
etc.)
does not work once the number of hours is more than 24hrs. Can anyone
suggest a formula please?
--
Regards
rbc

Thanks Ron, much appreciated.


"Ron Rosenfeld" wrote:

On Wed, 5 Jan 2005 05:01:02 -0800, rbc wrote:

I am trying to calculate distance, speed and time to cover the distance in
Excel spreadsheet.
I am using distance/speed to display the time in hours & decimals of an
hour, e.g. 28.50hrs [which is 28 hrs 30mins]; but can't come up with a
consistent formula to display the time in hours and mins. =TEXT (F2/24 etc.)
does not work once the number of hours is more than 24hrs. Can anyone
suggest a formula please?

=TEXT(F2/24,"[h]:mm")

The brackets around the 'h' allow it to display more than 24 hours.


--ron

Thanks Bernie, worked fine.

"Bernie Deitrick" wrote:

rbc,

=TEXT(F2/24,"[h]:mm")

or, more simply:

=F2/24

then format for custom [h]:mm

HTH,
Bernie
MS Excel MVP

"rbc" wrote in message
...
I am trying to calculate distance, speed and time to cover the distance in
Excel spreadsheet.
I am using distance/speed to display the time in hours & decimals of an
hour, e.g. 28.50hrs [which is 28 hrs 30mins]; but can't come up with a
consistent formula to display the time in hours and mins. =TEXT (F2/24
etc.)
does not work once the number of hours is more than 24hrs. Can anyone
suggest a formula please?
--
Regards
rbc