Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rbc
 
Posts: n/a
Default 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
  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #4   Report Post  
rbc
 
Posts: n/a
Default

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

  #5   Report Post  
rbc
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Excel: To Display Formula in the cells Instead of Value [email protected] Excel Discussion (Misc queries) 1 December 10th 04 10:48 PM
Paste is is copying in formula, but display is wrong. Matt Excel Worksheet Functions 2 December 7th 04 09:37 PM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 11:18 PM
Display excel formula on worksheet superchica Excel Worksheet Functions 2 November 3rd 04 07:38 PM


All times are GMT +1. The time now is 02:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"