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


--
Regards
Frank Kabel
Frankfurt, Germany


Stéf wrote:
> 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.

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"),".","'")





In article <Ox8kF4$(E-Mail Removed)>,
"Stéf" <(E-Mail Removed)> wrote:

> 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.

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 Removed)HIS


"Stéf" <(E-Mail Removed)> wrote in message
news:Ox8kF4$(E-Mail Removed)...
>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.
>
>
>
>