3

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.

Improve this question
2
  • 1
    Isn't Excel a bit heavy if you just want to convert a timestamp?
    – pipe
    Commented Mar 11, 2017 at 10:05
  • Maybe. I got a Biofeedback Program that gives me my psychophysiological data in Excel with this timestamp. But for Analysis Purposes i need a more handy time axis. I confiess iam a bit lost with that.
    – Marco Jajac
    Commented Mar 11, 2017 at 22:49

5 Answers 5

Reset to default
3

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)

Improve this answer
10
  • 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
    Commented Mar 11, 2017 at 22:51
  • 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!
    – Marco Jajac
    Commented Mar 11, 2017 at 22:59
  • What happens if you change the format of those cells (to general, to timestamp, to number)? What do the contents look like, then?
    – Glorfindel
    Commented Mar 12, 2017 at 7:42
  • 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.
    – Marco Jajac
    Commented Mar 12, 2017 at 9:17
  • Hmm ... strange. I can only test it by typing in these timestamps manually, and then it works. Blame it on the mysteries of Excel ...
    – Glorfindel
    Commented Mar 12, 2017 at 9:56
1

If you have 00:02:02.300 in a cell, it may appear exactly like this if it's formatted as text1, 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.2

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.
Improve this answer
2
  • The other answer doesn't include changing the formatting.
    – user287352
    Commented Feb 19, 2019 at 6:26
  • 1
    @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.
    – user287352
    Commented Feb 21, 2019 at 5:00
0

RE: likely reason you're getting the #VALUE! error

RE: the Value() function

Examples (enter and format cells using an xl worksheet)

A1 custom format as hh:mm:ss.000, then enter 16:48:30.001

B1 general format is fine, enter formula: =TIMEVALUE(TEXT(A1,"hh:mm:ss.000"))

C1 enter formula: =TIMEVALUE(A1)

Results:

A1 16:48:30.000 B1 0.700347222 C1 #VALUE!

Cell Formula Comments:

A1: 16:48:30.000 just a typed in VALUE, cell formatted as hh:mm:ss.000

B1: =TIMEVALUE(TEXT(A1,"hh:mm:ss.000")) this works TEXT(..) is convert the VALUE in A1 to TEXT in the format of "hh:mm:ss.000" which the TIMEVALUE function expects

C1: =TIMEVALUE(A1) - the fails with #VALUE! because A1 is a VALUE ALREADY, it's just been formatted to look like text.

Improve this answer
0

Format the Cell or Range Of Cells as shown in Screen Shot below, to record the Millisecond.

enter image description here

  • Next is enter the TIME, I've entered current time using, =Now().
  • You may use any of the Formulas to convert the Time (with Millisecond) into Total Seconds.

=L13*86400 or =L13*24*60*60 you get 38574.

Note:

  • Apply NUMBER format with Zero Decimal to the Cell, before you write these Formulas.
  • Adjust cell reference in the Formula as needed.
Improve this answer
0

This worked for me: 

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

Easy peasy.

The previous answer had the millisecond starting with the 9th character, but it actually starts on the 10th character, thus the '10' in the last calc. I also needed to multiply it times .001 to get the milliseconds to display properly.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .