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 - 17 Mar 2008 13:02 GMT
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 - 17 Mar 2008 15:08 GMT
>Excel is using a standard format in the Formula bar (of hh:mm:ss)
>rather than the format applied in the cell.
[quoted text clipped - 5 lines]
>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.
pub - 17 Mar 2008 16:26 GMT
>>Excel is using a standard format in the Formula bar (of hh:mm:ss)
>>rather than the format applied in the cell.
[quoted text clipped - 14 lines]
> 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
dg.courbat@gmail.com - 17 Mar 2008 15:11 GMT
> I have timing data from a stopwatch.
>
[quoted text clipped - 32 lines]
>
> 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