Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Converting Eastern time to Central time | Excel Discussion (Misc queries) |