computing time

  • Thread starter Thread starter Stéf
  • Start date Start date
S

Stéf

I would like to raise a formula which calculate a time value and display it
in a specified format from the distance and the speed.
e.g.
B1=42195 distance in m
A2=17 speed in km/h or *(10/36) = 4.722 in m/s

Time = B1/(A2*10/36) = 8935.412 s
I would like the following result displayed in B2: 02:28:55'41 which
corresponds to Time converted in hh:mn:s'(1/10).
I prefer a solution not using the excell function TIME.

Could anyone help?

Thanks, Stéphane.
 
Hi
use the formula
=(B1/(A2*10/36))/(24*60*60)

and afterwards format the cell with the custom format
[hh]:mm:ss.00
 
One way:

Since XL stores times as fractional days, you can convert to a time by
dividing by 86400 seconds per day, to get

=B1/A2/311040

Custom format as

hh:mm:ss.00

I don't know any way to get the ' character instead of the period, while
leaving the value a number. If you didn't mind a string, you could use:

=SUBSTITUTE(TEXT(B1/A2/311040,"hh:mm:ss.00"),".","'")
 
Stéphane

You need to convert the whole number you have. Which in excel signifies days
to decimals to signify time. That is 24*60*60=86400. The formula would
then be

=(B1/(A2*10/36))/86400

You then need to format the cells hh:mm:ss.ss.

For explanations of dates and times in Excel go to

http://www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top