Formatting time for m:ss.00

[John Boulton]

I would like to be able to format columns/cells to read time inthe format
m:ss.00 or alternatively m:ss:00 (for determining beaten margins of
racehorses the finishing times are given in the above formats but excel
doesn't appear to want to mix or match them
any assistance would be appreciated
JB

 

[Peo Sjoblom]

It is always better to use hours as well even if you don't enter any

hh:mm:ss.00

then enter 2 minutes 30 seconds and 2 10ths of a second like

00:02:30.2

works for me

 

[OssieMac]

Hi John,

I don't know why you are having problems. Formatting as Time then Custom you
can format as m:ss.00

 

[John Boulton]

My Apologies,
I am in the early stages of construction of a particular worksheet/book.
Thank you for your advice, I really asked the wrong question -- I can format
the column as you have indicated, my problem is , I now wish to treat that
result(for further calculations) as a number
I can use a simple formula for estblishing the beaten margin (in times i.e.
ss.00) but i wish to use that figure in a further calculation (i.e. convert
time beaten /to- distance beaten /to- weight margin)the formula is simply
beaten margin(time in seconds.00)*6 (number of lengths to a second(( a
constant)))*number of kilograms to a length(1.5 for this exercise) this
calculation can be achieved with te simple formula =X(the beaten
margin)*6*c(c=1.5 or whatever weight value I use for a length) unfortunately
I cannot seem to get this formula to work unless x is a number not a time
Long winded I know but my powers of description are limited
Tks
JB

 

[Pete_UK]

Excel stores times as a fraction of a 24-hour day, so that 6:00 (or 6
hours) is actually stored as 0.25. If you want to use time information
in a calculation, then you must multiply by the appropriate factor. In
your case this would be *24*60*60 as you want the time to be in
seconds - just add that part to your formula.

Hope this helps.

Pete

 

[John Boulton]

Bingo!! my faith in numbers is once again restored, and a little further
knowledge gained.
My thanks to you for your assistance
JB