Time of Day vs Measured Time

[Jeff]

I am just starting to record some times from my son's
swimming events. I eventually would like to do
calculations to show his progress in tables and charts.
The problem I have is Excel converts any time formatted
input such as mm:ss.00 to a time of day instead of just
leaving it as a measured amount of time. It's
frustrating because it shows AM & PM on data and rounds
the decimals automatically when a cell is simply opened
for editing.

For instance a time shown as 1:35.02 in a cell displays
in the edit box as 12:01:35 AM when double clicked.
Also, negative times are displayed as ######## when they
might be valid if you want to calculate a drop in time
from one swimming event to another.

Is there any way to set it to perform time based
calculations but not treat the data as a time of day?

I know that I can do this myself by converting everything
to seconds, doing the math, and then converting back to
minutes & seconds but it will take a lot more formulas
and concatenation for each step.

 

[Earl Kiosterud]

Jeff,

The displaying of times is controlled by the formatting in the cell. Try
Format - Cells - Number - Custom, and enter mm:ss.00

 

[J.E. McGimpsey]

XL stores elapsed times and times of days identically - as
fractional days (e.g., 1:00 = 1/24). To get elapsed time, choose a
hh:mm:ss format (Format/Cells/Number/Time).

XL interprets 1:35 as 1 hour, 35 minutes. You can't change that, so
you can enter 0:1:35.02, enter minutes and seconds separately and
combine them in a third cell:

A1: 1
B1: 35.02
C1: A1/(60*24) + B1/(60 * 60 *24)

or you can modify the code here:

http://cpearson.com/excel/DateTimeEntry.htm

For negative times, change your Date system to 1904
(Tools/Calculation, check the 1904 date system). Note that any
existing dates in the workbook will change by 4 years and a day (new
dates will be entered correctly).

 

[J.E. McGimpsey]

Forgot:

To display fractional seconds, use

Format/Cells/Number/Custom mm:ss.00

Note that you'll still have to enter hours:

0:1:35.02

 

[Jeff]

Earl

Done that. That's were the problem is based. That
format seems to assume Time of Day and not just a
measured amount of time.

Thanks

 

[J.E. McGimpsey]

To XL, there's no difference - if the value 0.125 is stored in a
cell, formatting it as time of day (e.g., "hh:mm AM/PM") will
display 3:00 AM, while formatting it as elapsed time (e.g., "hh:mm")
will display 3:00.

 

[hmm]

Jeff,

Enter the elapsed time (ie. one-and-a-half hrs. is entered as 1.5),
let's say in cell A1. In another cell, set to time format and enter the
formula =A1/24. I think that'll do what you want!