Converting hh:mm:ss.nnn to mm.000

[Three Lefts]

I have timing data from a stopwatch.

The readout can be displayed in two ways:

1. Up to 40 minutes, the display contains mm:ss:dd, where mm =
minutes, ss = seconds, and dd = deciseconds (100ths of a second, not
milliseconds).

2. For 40 minutes or more, the display shows hh:mm:ss.

I would like to enter the raw data in a Stopwatch cell and have it
display as it was recorded from the stopwatch and then convert it to
minutes in a Minutes cell. Most of the times will be well under an
hour.

Some examples:

Stopwatch Minutes
30:00:00 30.000
30:10:00 30.167
30:30:00 30.500
30:30:10 30.502
30:30:25 30.504
30:30:50 30.508
30:30:99 30.517

I formatted the Stopwatch cell (A1) as "mm:ss.00".

In the minutes cell, I put "=A1*24*60".

This works perfectly except that when I select the Minutes cell, the
contents displayed in the formula bar are missing the deciseconds. If
I enter "30:30.99", it displayes in the cell as "30:30.99", but in the
formula bar, it shows "00:30:30".

What is going on here?

 

[stew]

Excel is using a standard format in the Formula bar (of hh:mm:ss)
rather than the format applied in the cell.

This is similar to dates. eg Enter the date "17/03/2008" and change
the format to display it as "17 March 2008". If you then click on the
cell you will still see "17/03/2008" in the formula bar.

Not sure whether this helps, but may at least explain what you are
seeing!

 

[Three Lefts]

Excel is using a standard format in the Formula bar (of hh:mm:ss)
rather than the format applied in the cell.

This is similar to dates. eg Enter the date "17/03/2008" and change
the format to display it as "17 March 2008". If you then click on the
cell you will still see "17/03/2008" in the formula bar.

Not sure whether this helps, but may at least explain what you are
seeing!

Got it, thanks.

I don't suppose there's a way to get Excel to display exactly what I
originally typed. I guess that would require Excel to store exactly
what I typed. Excel does some work on my input data and store the
result. If I enter "3/17", Excel stores the data-time number
representing 3/17/2008. My original characters are not stored
anywhere.

 

[dg.courbat]

I have timing data from a stopwatch.

The readout can be displayed in two ways:

1. Up to 40 minutes, the display contains mm:ss:dd, where mm =
minutes, ss = seconds, and dd = deciseconds (100ths of a second, not
milliseconds).

2. For 40 minutes or more, the display shows hh:mm:ss.

I would like to enter the raw data in a Stopwatch cell and have it
display as it was recorded from the stopwatch and then convert it to
minutes in a Minutes cell. Most of the times will be well under an
hour.

Some examples:

  Stopwatch    Minutes
   30:00:00     30.000
   30:10:00     30.167
        30:30:00     30.500
   30:30:10     30.502
   30:30:25     30.504
   30:30:50     30.508
   30:30:99     30.517

I formatted the Stopwatch cell (A1) as "mm:ss.00".

In the minutes cell, I put "=A1*24*60".

This works perfectly except that when I select the Minutes cell, the
contents displayed in the formula bar are missing the deciseconds. If
I enter "30:30.99", it displayes in the cell as "30:30.99", but in the
formula bar, it shows "00:30:30".

What is going on here?

Hello,
I can not reproduce your problem.. but if you try to format the cell
like this : [m]:ss.00
Look in Help : format number
DG

 

[pub]

Got it, thanks.

I don't suppose there's a way to get Excel to display exactly what I
originally typed. I guess that would require Excel to store exactly
what I typed. Excel does some work on my input data and store the
result. If I enter "3/17", Excel stores the data-time number
representing 3/17/2008. My original characters are not stored
anywhere.

i had this same problem when i was working with milliseconds.
the only way i could make it work properly was
- format the input column as "Text" call it cell A1
- then cell B1 excel would allow me to convert to time format =A1+0
- then i could use whatever formula i wanted like if i wanted
milliseconds =right(a1,4)+0 or else i could use =seconds(b1)

that was my solution
but you can also do the math 24*60*60*1000 is 86400000
good luck