Adding Stopwatch Time Laps

[AerospaceAdams]

Good afternoon,

I am working on a project that required me to gather almost 150 stopwatch laps using the Windows 10 stop watch feature. In the past I would use the stopwatch feature on my phone and then send the data to my email and copy it to excel where I would use the "text to columns" feature and separate out my hours, minutes, and seconds into individual columns, then use a few formulas to get the total time for different categories. I mention that up because Excel will not allow me to do that with my current data. I have attempted to change the cell formats to different time formats (as many google search results recommend) and do it that way, but it still only returns "#VALUE!". I have even tried dropping the milliseconds, and that doesn't work.

I'm getting frustrated, and don't want to redo a four hour time study. I'm not afraid to use macros/vba or any other round about ways, so if anyone has any kind of solution, any help would be appreciated.

Here is an example of the data that I am using:

00∶01∶17.74
00∶07∶29.89
00∶00∶39.75
00∶00∶44.25
00∶01∶23.94
00∶03∶36.41
00∶02∶22.91
00∶01∶25.19
00∶00∶22.84


Thank you,
Amy

 

[DRSteele]

Welcome to the forum.

I don't know if I'm on the right track here. I pasted your data into Excel and tried to use TextToColumns with the colon as the delimiter and it did nothing. It turns out that the colon you've pasted above is not the ANSI character colon typical for Excel. It is character code 63 (for some weird reason) and not 58 as it should be. I discovered that by using the function Code.

So just copy one of the so-called colons from your data: Select one of the cells, put it in edit mode, carefully select one of the so-called colons, select copy, escape. 1) You can select all your data and use the Replace operation by pasting the so-called colon in the find what field and in the replace with field typing the real colon on the keyboard. Or 2) Paste the naughty colon into the delimiter part of TextToColumns. Then, all the data should be split into columns that you can deal with.