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

G

Guest

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?
 
R

Ron Rosenfeld

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?
Click to expand...

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

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


--ron
 
B

Bernie Deitrick

rbc,

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

or, more simply:

=F2/24

then format for custom [h]:mm

HTH,
Bernie
MS Excel MVP
 
G

Guest

Thanks Ron, much appreciated.


Ron Rosenfeld said:
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?
Click to expand...

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

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


--ron
Click to expand...
 
G

Guest

Thanks Bernie, worked fine.

Bernie Deitrick said:
rbc,

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

or, more simply:

=F2/24

then format for custom [h]:mm

HTH,
Bernie
MS Excel MVP

rbc said:
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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel - Time display 4
Macro / function text time to 24hr excel time passed midnight fortotaling hr's 2
Time Sum in Excel 1
how can i convert a value quoted in hrs,mins,secs to just mins 1
how do i find # hrs & mins. between 2 different dates and times 3
How do I convert hours and mins into just minutes in excel? 1
Calculate hours, past midnight etc 4
Conditional Time Calculation 10

Top