Formatting times for athletic events

[Steve Foulks]

I want to enter data relating to swimming times into to a
worksheet and calculate statistics for the data. For
example a time for a 100 yard breaststroke might be 1
minute, 4.2 seconds. How would I enter such data, and how
would I format the results? All of the time formatting
options seem like they relate to time of day, not the kind
of :times" I am talking about.

 

[Norman Harker]

Hi Steve!

Use for your example:

00:01:04.2

But you can format mm:ss.00

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Thanks Norman,

However when I look at the result in the formula bar, I
get this:

12:01:04 AM

Is that what you get?

If I enter 00:01:05.7 with the formatting you mention, you
get 12:01:06 AM in the formula bar (It rounded it up from
5.7 to 6). It drops the number after the decimal point in
the formula bar. This is awkward and confusing.

There should be a specific time formatting that isn't tied
to the time of day, in my humble opinion.

 

[Norman Harker]

Hi Steve!

Yes. But don't worry! I think that Excel's approach here is confusing
but it does need to use an hour in it'd representation of the time and
chooses to denote the first hour of the day as (eg) 12:00:01 AM rather
than 00:00:01. It might also be formatted as dd-mmm-yyyy hh:mm:ss.000
and that would show you that Excel calculates all dates and times from
midnight on 30/31 Dec 1899.

There certainly is confusion between clock times and elapsed times but
I think that confusion is due to our way of looking at them
differently. After all, clock times are also elapsed times, aren't
they? A clock shows us the elapsed time from midnight or (12 hours
clock) noon.

With elapsed times we always work from a base of 0 on a stop watch but
we could use stop_time - start_time.

Trust me!! You really will get used to it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.