Convert Timestamp 00:00:00.000 into total seconds AND milliseconds

Question

I have the problem that I am left with a timestamp that reads like: 00:02:02.300 meaning: hours - minutes - seconds - milliseconds.

and need to desperately convert it to total seconds. That is tricky because i have the milliseconds bit in there and there is neither a clear format for that in Excel nor is it possible to easily "clip" the milliseconds away. In the best case scenario I would need total seconds AND milliseconds like 6528.32 or something like that.

Solution 1

If you're certain the timestamps always come in that format (HH:mm:ss.SSS), you can use the following Excel function (provided that the timestamp is in cell A1, and it's formatted like text):

=VALUE(RIGHT(A1, 6))+60*VALUE(MID(A1, 4, 2))+3600*(LEFT(A1, 2))

For a timestamp of 00:02:02.300, this will give the result 122.3.

If it is formatted as a date, it's even simpler:

=A1*86400

(because Excel stores times as fractions of a day; e.g. 12:00:00 is stored as 0.5 because it's half of 24 hours)

Solution 2

If you have 00:02:02.300 in a cell, it may appear exactly like this if it's formatted as text¹, or it may appear differently if it's formatted like something else.

The trick is easy to get seconds and miliseconds only, that is 122.300.

First, assume 00:02:02.300 is in cell A1. Highlight it and format it as a number. It will display something that looks like nonsense. Probably 0.00. Go to cell B1 and enter =A1*86400. Cell B1 will now contain 122.3. To get trailing zeros, format B1 as a number and adjust to your tastes.

To give Glorfindel's VALUE solution correctly, the formula would be

=VALUE(3600*LEFT(A1,2))+VALUE(60*(MID(A1,4,2))+VALUE(MID(A1,7,2))+VALUE(MID(A1,9,3))

I'll explain what this formula is doing. It is looking at the cell A1 as a text string, then converts parts of it to numbers, then adds them together appropriately to make them into total seconds. It has four parts:

1. VALUE(3600*LEFT(A1,2))
2. VALUE(60*(MID(A1,4,2))
3. VALUE(MID(A1,7,2))
4. VALUE(MID(A1,9,3))

Looking at the string 00:02:02.300, you can see it is basically four numbers separated by : and .. Each of those four numbers corresponds to the four parts above, from left to right.

VALUE(3600*LEFT(A1,2)) is converting 00, the hours place, to seconds. Item two works on the next number 02, converting it to minutes. And so on. Now, notice in the full formula that each of the items in the list above are added + together. That gives you the seconds and the decimals, 122.3. If you are still having trouble understanding what this formula does, type it out slowly in Excel and pay attention to the formula hinting that pops up automatically. It tells you exactly what each part does.

If you put all the items in the above list into their own separate cells, your output would be:

1. 0
2. 120
3. 2
4. 0.3

Put it together and what do you get? Bibbidi Bobbidi Boo! Add them up and it's your answer 0 + 120 + 2 + 0.3 = 122.3.²

To further keep your data organized and under your control, I recommend copying all the new cell data with your seconds and decimals how you like them, then paste as values only into new cells.

---

1. If A1 is already formatted as text, leave it. Excel still uses it correctly in the formula in cell B1. Caution, converting back and forth between formats can permanently mess up your data. I strongly recommend working off a copy of your data, rather than the main file, because when you start playing with formats and text-to-number conversions (what the VALUE formula does), there is a good chance you accidentally and permanently change your data to something unusable.
2. At this point you might ask, why separate the numbers 02 and 300? the .300 is parts of a second and 02 is also seconds. They are the same thing. Well, your data in the future might have a peculiar separator in the future, like , or ;. Data I have separates it with ; and the trailing numbers are actually frames of a video within that time frame, so I have to do additional calculations on it to get decimal-seconds.

Comments

[Marco Jajac]

First, let me thank you very much for answering me, Dear Glorfindel. Problem is that it does not work. I get #Value! Error with the =A1*86400 Approach

[Marco Jajac]

With the other Approach i get that "there is something wrong with the formula". This timestamp comes from a program that measures psychophysiological data (heartrate, skin conductance) with the HH:mm:ss.(Miliseconds) Data stored in STANDARD under Custom in the cell format. Maybe that is the root of the problem? Thanks for at least trying to help me, mate. I appreciate it!

[Glorfindel]

What happens if you change the format of those cells (to general, to timestamp, to number)? What do the contents look like, then?

[Marco Jajac]

When i "change" the format pretty much NOTHING happens. But here is the thing: When i "clip" away the miliseconds bit from the time stamp i can suddenly change it into total seconds! Meaning: 00:02:02.300 - erase .300 - THEN: 00:02:02 is left - type [ss] into type in custom and presto i get the total seconds of 122. Before the change in [ss] the (minus milisecond bit) timestamp reads: hh:mm:ss. But with the original Timestamp it only reads STANDARD. I think that is part of the problem. I tried to type in hh.mm.ss.000 but Excel did not accept it.

[Glorfindel]

Hmm ... strange. I can only test it by typing in these timestamps manually, and then it works. Blame it on the mysteries of Excel ...

[Marco Jajac]

It is ok. Maybe you will find something in the coming days or so. No pressure on you. But maybe something will occur to you. I thank you for your kind help up to this point. I expect nothing from you but i am glad for all the help i can get. Nice Sunday to you, Sir.

[Marco Jajac]

Glorfindel is is perhaps possible to send you somehow a part of my timestamp so you can just look at it? I do not wanna give the idea that i somehow want to burden you with work or something, it is not about that at all. It is just that i am completely stuck with this timestamp bit and you are obviously far more an expert i could ever be. Again: It is not about you doing anything really, just tell me what is wrong with it. Is there a possibility for it or did i just overstep now? If so, i am sorry. Did not want to upset you at all.

[Glorfindel]

Sure, my e-mail address is <my twitter handle> at Gmail dot com. Note that because we probably use different versions of Excel (I use Excel for Mac 2011), our results may vary.

[287352]

Can't you just format as a number then multiply by 86400? The whole =VALUE string is unnecessary.

[287352]

The other answer doesn't include changing the formatting.

[287352]

See my updated answer. Your VALUE formula is incorrect in a lot of ways.

[287352]

@MátéJuhász I updated. Glorfindel's VALUE formula is wrong in a lot of ways. That may have been OPs problem. Further, it gave no explanation what the formula does.