InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
Solve : MS Excel time format question? |
|
Answer» Hi All, No matter what I have tried I cannot get excel to take the time format 00:00:00:00:000 and convert it to a decimal.Seems pretty obvious to me Excel will not convert 00:00:00:00:219 to a decimal. Exacfly what part of that date/time sequence do you want converted to a decimal? If you were just working with hours and minutes, then I believe a formula can be used to convert the minutes to a decimal portion of an hour. For example, 1 hour 30 minutes could be converted to 1.5 hours. Quote from: LottieB on August 03, 2010, 03:18:47 PM Hello Chuck Thanks for the help. No, the first set of data is not the same as the second set. I copied data from an earlier/older excel file that the data was imported using the original Benchlink Data Logger 1 that puts the time in decimal format itself. The second set of data is from the newer Benchlink Data Logger 3 that uses the day:hour:etc... format.Quote from: Computer_Commando on August 03, 2010, 03:34:18 PM It's called Parse Unfortunately I don't think this will work because, as mentioned by soybean, my data is generally goes for around 9 minutes long and sometimes into the hours as well. Never days though. Thanks for looking at this. I am no EXPERT in excel but it's one of those things that I know we "should" be able to do, but just can't for some reason.Quote from: soybean on August 04, 2010, 07:09:27 AM Computer_Commando, I believe we need to know whether ChuckSteak ever has data other than 0s in the day:hour:minute:second columns. If so and if those data elements are needed, then parsing into columns and deleting the four columns representing day:hour:minute:second would not work. My test generally runs about 9 minutes. At the rate the data acquisition logger is scanning this means about 5500 scans in that 9 minutes. So that's 5500 cells worth of data that needs to be converted. Sometimes I run a longer test, but SLOW down the scan speed of the data acquisition logger. But for time I run tests ranging from 9 minutes to 7 hours.Is this what you need? http://tinypic.com?ref=200u22s" target="_blank">http://i35.tinypic.com/200u22s.jpg" border="0" alt="Image and video hosting by TinyPic">[/url]Quote from: LottieB on August 04, 2010, 11:20:25 AM Is this what you need? I tried that after seeing your first reply. Unfortuantely it doesn't get me what I need. I need the time to be in decimal format. If we can "trim" off the extra data then convert it to a decimal I would be golden. In short if the end result is 00:xx:xxx it still doesn't work for me. Thanks again for the help.Hi Chuck I think you need to give me more information as to how the last 3 digits relate to time. For example, I was assuming the .219 bit (in A3 in my example) is a fraction of a second, i.e. 219/1000 of a second. I take it this isn't so?Quote from: LottieB on August 04, 2010, 01:39:27 PM ...I think you need to give me more information as to how the last 3 digits relate to time.He said is was so: "...looks like straight time in day:hour:minute:second:milliseconds..." He needs the entire cell converted to seconds. Back to parsing & then multiplying: (A * 86400) + (B * 3600) + (C * 60) +D + E = Elapsed Time (Seconds)Quote from: Computer_Commando on August 04, 2010, 02:06:46 PM He said is was so: "...looks like straight time in day:hour:minute:second:milliseconds..." Exactly. And if you look at the pic I posted, the last part of the number is a decimal to 1000. And one millisecond is 1/1000th of a second is it not? The 00:00 part is separated by COLONS and if you create a custom number format to interpret as a time value then Excel will read the whole thing as time - provided the initial exported data has had one set of 00S removed (which is why parsing doesn't work as it removes the colons and I'm not entirely sure the remaining data wouldn't still be being interpreted at text, so a VALUE function will still be needed along with something to substitute the final set of colons with a DP). Well that's the way I'm seeing it at the moment ...I shall not be defeated OK Ignore my last post I hadn't realised Chuck wanted the other time parts converted to a number too, I was thinking it was just the fractional part. Is this what you want Chuck? In which case use Data Text to Columns (the ole parsing) to a general format and then do a formula to get your time conversion. Quote from: Computer_Commando on August 04, 2010, 02:06:46 PM He said is was so: "...looks like straight time in day:hour:minute:second:milliseconds..."I forgot to divide "E" by 1000. (A * 86400) + (B * 3600) + (C * 60) +D + (E/1000) = Elapsed Time (Seconds) Now, we're in agreement, except you need to add hours, too. He said "...my data is generally goes for around 9 minutes long and sometimes into the hours as well. Never days though..." Can't hurt to add them all, since days will be 0.a VBA Macro can do the trick. Alt-F11. Choose Insert->Module from the Visual Basic Editor that appears. Paste the following into that module: Code: [Select]Option Explicit Public Function BenchLink_Format(ByVal BenchLinkData, Optional ByVal Separator As String = ":") As Date '00:00:00:00:219 Static flInit As Boolean, Stepamounts() As Double Dim SplitParts() As String If Not flInit Then flInit = True ReDim Stepamounts(0 To 4) Stepamounts(0) = 60# * 60# * 24# Stepamounts(1) = 60# * 60# Stepamounts(2) = 60 Stepamounts(3) = 1 Stepamounts(4) = 0.001 End If Dim ReturnValue As Variant, I As Integer ReturnValue = 0 SplitParts = Split(BenchLinkData, Separator) For I = 0 To UBound(Stepamounts) ReturnValue = ReturnValue + CDec(Val(SplitParts(I)) * Stepamounts(I)) Next I BenchLink_Format = ReturnValue End Function now use it in your spreadsheet as follows: Code: [Select]=BenchLink_Format(Value) in the test spreadsheet I had column A contain the data you provided. Column B was just =BenchLink_Format(A1) set to fill downward. The resulting spreadsheet looks something like this: Code: [Select] 00:00:00:00:000 0 00:00:00:00:219 0.219 00:00:00:00:323 0.323 00:00:00:00:427 0.427 00:00:00:00:530 0.53 00:00:00:00:635 0.635 00:00:00:00:738 0.738 00:00:00:00:840 0.84 The result is represented in seconds. |
|
