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?

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!

On Mon, 17 Mar 2008 05:02:50 -0700 (PDT), stew
<(E-Mail Removed)> wrote:

>
>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.

On 17 mar, 08:53, Three Lefts <spamt...@spamtrap.invalid> wrote:
> 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

Three Lefts <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> On Mon, 17 Mar 2008 05:02:50 -0700 (PDT), stew
> <(E-Mail Removed)> wrote:
>
>>
>>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.

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