Converting Time
 

Hi


Using excel 2007

I have Column A which has cells with lots of times in them.

01.0.14
01.2.24
59.55
57.01

Min.Sec.MilliSeconds
Formatted as General

I need to work with these so I can Add, Subtract, Etc.

I've tried formatting as mm:ss.00 and using text to columns, etc. With no
joy


Hope someone can help


DaveM

Converting Time
 

On Mon, 5 Jul 2010 18:55:32 +0100, "DaveM"
wrote:

Hi


Using excel 2007

I have Column A which has cells with lots of times in them.

01.0.14
01.2.24
59.55
57.01

Min.Sec.MilliSeconds
Formatted as General

I need to work with these so I can Add, Subtract, Etc.

I've tried formatting as mm:ss.00 and using text to columns, etc. With no
joy


Hope someone can help


DaveM


Well, you are performing time math, however, you are breaking it down in
a way that Excel's cell formatting and handling code does not parse.

You can pre-parse the milliseconds out of the number, OR you can
physically begin making entries which span more than one cell, so that
your minute math, and your millisecond math can be separately processed.

Multiple cells would be how I would do it, as parsing would only work
if EVERY cell had data in it in the same format. That would mean that
all of you whole number entries would nee to have.00 added to them to
show zero milliseconds, but also to allow the strictly character based
parser to correctly parse the cell's data.

Here is how Excel tracks time:

Excel only tracks time down to a 1 second interval. The decimal portion
of the Time value is the number of seconds from Midnight. There are
86400 seconds in a 24 hour day. The example given is for the worksheet
using column "A". A1 contains the value 0.453240.

EXAMPLE:

'Time in seconds since Midnight
A1 0.453240

'Convert from decimal to integer
A2 =A1*100000

'Calculate Hours
A3 =INT(A2/3600)

'Calculate Minutes
A4 =INT(MOD(A2, 3600)/60)

'Calculate Seconds
A5 =MOD(A2, 3600) - (A4*60)

So, you would need to parse your data and shift the decimal portion to
another cell, then convert your whole minutes and seconds into the excel
absolute time format, then do the maths on your decimal data, then do
maths on your whole figure data, then add that compiled data to your
whole number time maths.

The other way is to parse the entries into three cells, format them
numeric, not time, and then do the maths, and then calculate the time via
formula as in the above.

Converting Time
 

Will try

Thanks for the reply


"StickThatInYourPipeAndSmokeIt" wrote in message
...
On Mon, 5 Jul 2010 18:55:32 +0100, "DaveM"
wrote:

Hi


Using excel 2007

I have Column A which has cells with lots of times in them.

01.0.14
01.2.24
59.55
57.01

Min.Sec.MilliSeconds
Formatted as General

I need to work with these so I can Add, Subtract, Etc.

I've tried formatting as mm:ss.00 and using text to columns, etc. With no
joy


Hope someone can help


DaveM


Well, you are performing time math, however, you are breaking it down in
a way that Excel's cell formatting and handling code does not parse.

You can pre-parse the milliseconds out of the number, OR you can
physically begin making entries which span more than one cell, so that
your minute math, and your millisecond math can be separately processed.

Multiple cells would be how I would do it, as parsing would only work
if EVERY cell had data in it in the same format. That would mean that
all of you whole number entries would nee to have.00 added to them to
show zero milliseconds, but also to allow the strictly character based
parser to correctly parse the cell's data.

Here is how Excel tracks time:

Excel only tracks time down to a 1 second interval. The decimal portion
of the Time value is the number of seconds from Midnight. There are
86400 seconds in a 24 hour day. The example given is for the worksheet
using column "A". A1 contains the value 0.453240.

EXAMPLE:

'Time in seconds since Midnight
A1 0.453240

'Convert from decimal to integer
A2 =A1*100000

'Calculate Hours
A3 =INT(A2/3600)

'Calculate Minutes
A4 =INT(MOD(A2, 3600)/60)

'Calculate Seconds
A5 =MOD(A2, 3600) - (A4*60)

So, you would need to parse your data and shift the decimal portion to
another cell, then convert your whole minutes and seconds into the excel
absolute time format, then do the maths on your decimal data, then do
maths on your whole figure data, then add that compiled data to your
whole number time maths.

The other way is to parse the entries into three cells, format them
numeric, not time, and then do the maths, and then calculate the time via
formula as in the above.

Converting Time
 

Hey Dave.

If you are by chance still out there, I made a sheet for you. It is
at:

http://www.mediafire.com/file/zzmii2jmibl/TimeWorks.xlt



On Tue, 6 Jul 2010 18:25:21 +0100, "DaveM"
wrote:

Will try

Thanks for the reply