0

I've got a big csv with head movement data that I need to combine with an audio recording. The timecode in the csv doesn't start at 0, so I need to subtract the starting time from all entries.

The starting timecode looks like: 00:36:16:15.295

It's a bit confusing, I'm not certain what each number signifies. They all go to 59, except the part after the dot, which goes to 999.

How do I make the column start at a timecode of 00:00:00:00.000?

June
  • 25
  • 9
  • 2
    The second section going to 59 rather than 24 or 99 makes me wonder whether it is possible that we are looking at hh:mm:ss:ff.fff (see https://english.stackexchange.com/questions/51860/is-there-a-word-for-a-60th-of-a-second) Does the first section really have a 59 in it? I confess I don't understand the actual question - can you give an example of desired input and output? – zsalya Jun 29 '22 at 19:33
  • 1
    can i just check is that how the time is stored in the CSV or how it's being displayed in excel when you open the csv? also can excel read that as a time, if so it will likely convert it to a number underlying a time format, which would make your question a lot easier to solve. – InjuredCoding Jun 29 '22 at 22:07
  • @zsalya So it goes up to `00:36:22:59.931` and then the next entry is `00:36:23:00.933`. I think it might be hh:mm:ss:ff.fff, since the whole thing should be about 7 minutes long, and it starts with 00:36... and ends with 00:43... The three digits at the end sometimes skip a number. So it seems like a 60fps timecode. @InjuredCoding It is how it is being stored in the CSV. It doesn't seem like excel can read it as a time. – June Jun 30 '22 at 08:05
  • 00:36:22:59.931 with next entry 00:36:23:00.933. Then perhaps the last three digits are a serial number which is separate from the four pairs of numbers? – zsalya Jun 30 '22 at 20:33
  • @zsalya The last 3 digits go up to 999 and then reset. They do sometimes skip 1-3 numbers, but they are still incremental. I haven't found an answer yet, but I did solve it by using the `Data > Text to Columns...` feature, removing the frame info (##.###) and then using the `TIME` formula back to a timecode and subtracting 36 and 16 in the process. – June Jul 01 '22 at 08:29

2 Answers2

0

This is not a satisfying answer, but it is a solution to the problem at hand:

Using Data > Text to Columns... I split the timecode 00:36:16:15.295 into four separate columns: 00 | 36 | 15 | 15.295.

I then deleted the last column and created a new column with the following formula: =TIME(A3,SUM(B3,-36),SUM(C3,-16)). Using Format > Cells... I turned the resulting values into a time.

June
  • 25
  • 9
0

Are you sure about what you are saying?
You have the following timestamp:

00:36:16:15.295

This is of the form xx:xx:xx:xx.xxx.
Most timestamps are of the form hh:mm:ss.sss, but you seem to have an extra one, so seem to think that it is of the form dd:hh:mm:ss.sss (where d stands for "days"). But then you say that your amount of hours might go up to 59, which makes no sense.
So I would think that you are dealing with the form hh:mm:ss:ss.sss, where ss:ss.sss stands for seconds, hundreds of seconds, and hundred thousands of seconds (it's just a case of extreme accuracy).
This would explain why the first entry might go up to 59.
It also means that the second last entry (15 in your case) might go up to 99, instead of 59. Can you check if this is true?

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • Thank you @Dominique. As previously mentioned, the second last entry starts at 00 and ends at 59. I am pretty sure it is the frame. The timestamp is then `hh:mm:ss:ff.xxx`. I am not certain about the last three digits, since they go up to 999, but tend to skip numbers. For my solution I did not need to know the frames. – June Jul 01 '22 at 09:23