Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Three Lefts - 17 Mar 2008 08:53 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.