How to format time as in tract or lap time.

[ojlise]

I am trying to graph some of my daughters swimming times, but the
problem arises when time are larger than 1 minute. 56.2 sec appears as
56.2, 1 min 3.2 sec appears as 1:03.2. The problem is, 56.2 is a
number, 1:03.2 is really 12:01:03 AM. These numbers are not the same.
How can I enter times as minute and seconds with out Excel formatting
it as time of day?

 

[Bob Phillips]

The fact that it is a time of day should not be a problem, as a time of day
is relative (to the start of the day) just as a laptime is relative (to the
start of the lap).

If you ensure that the cells are formatted as hh:mm:ss.0, and not as
hh:mm:ss AM/PM, it should show okay, you will be able to do math on it, and
you should be able to get all analyses that you need.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peterso]

Here are some times that I copied from the web that lists the swim
times.
58.46
51.2
46.37
01:07.2
57.93
59.75
59.7
01:01.1
01:04.2
54.36

When I format these times in mm:ss.0 I get the following
02:24.0
48:00.0
52:48.0
01:07.2
19:12.0
00:00.0
48:00.0
01:01.1
01:04.2
38:24.0

???

How do I format the first set of numbers such that they make sence,
54.36 is 56.36 sec and 01:01.1 is 1 min 1.1sec?

 

[Pat Garard]

G'Day OJ,

1. Use the custom format string: mm:ss.0
(remember to enter 56.2 as 00:56.2 AND
enter 56.0 as 00:56.0).
2. Ignore what Excel displays in the Formula bar - the sheet display
will be what you require.
3. Addition, Subtraction, Averaging etc will all work.

 

[Bob Phillips]

Excel's times are fractions of 1 day, whereas the numbers you copy from the
web are in seconds (I presume). You need to convert to Excel time, so
assuming the copied values are in column A, add this formula in column B and
format and work on B

=A1/86400

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peterso]

Thanks Pat, I think I am getting closer. This is what happens when
copying and pasting from the web. Here are some of the original data
CMSA-SE 1:07.22 57.81
CMSA-SE 57.93 58.00
GPAC-SE 59.75 59.21
Bmac-SE 59.70 1:00.05
CMSA-SE 1:01.12 1:00.72
Bmac-SE 1:04.20 1:01.49
CMSA-SE 54.36 1:05.08

When these are pasted into Excel, all the times under 1 min are
automatically formatted at General (number), the rest are formatted as
time, i,e 1:05.08 shows 12:01:05 AM when you click on the cell. If I
concatenate the under 1 min time with =Concatenate("00:",A4) A4 here
is 57.81, the results shows 00:57.81 and is left aligned meaning it is
a string. So now all the under 1 min are strings and the over 1 min are
time??

 

[Pat Garard]

G'Day (again),

Excel dates are basically ORDINARY NUMBERS (with special storage)
that Excel is 'trained' to interpret as dates/times.

The format for ALL date/times within Excel is ......027.39832......
- a long decimal number.

In the above case, 27 is a number of days and .39832 is a (straight) decimal
fraction of a day.

Left to it's own devices, Excel interprets this as 27.39832 days since 12:00AM
on 1 Jan 1900 - i.e. 27 Jan 1900 9:33:34.8 (AM).

With a format of mm:ss.0,
the NUMBER 58.46 is 58.46 DAYS - 27 Jan 1900 11:02:24.0
displayed as 02:24.0 per your format.

If you enter 00:58.46, this, together with your format, is sufficient to enable
Excel to 'get it right'.

 

[Peterso]

Bob:

You solved my problem. Excel just needed to know that 56.1 is seconds,
by divinding all under 1 min numbers by 86400. Now all the numbers are
the same. Thanks,
Peter

 

[Pat Garard]

For 27 Jan 1900 11:02:24.0
Read 27 Feb 1900 11:02:24.0

With a format of mm:ss.0,
the NUMBER 58.46 is 58.46 DAYS - 27 FEB 1900 11:02:24.0
displayed as 02:24.0 per your format.

Sorry for the confusion!

 

[Pat Garard]

Top Stuff,