time code data formatting

Hi there,

I am working on some data cleanup for some video. I have a lot of time code data, ie. mm:ss (minutes; seconds), and I need to convert it to hh:mm:ss:ff (hhi is hours; ff is frames). It's A LOT of data, and I don't want to do it by hand. Examples:

2:02
0:55
2:18
2:55
1:41
1:20
1:44

I have two quesxtion:

1. Excel wants to interpret my data as time data and will convert my data if I click on a cell, ie. 2:02 is converted to 2:02:00 AM. That might not be so bad, but sometimes it changes a time that is 00:55 to 12:55:00. So, my first question is...how do I make excel read the numbers exactly as is w/o any conversion? I've tried to mark the cells as Text but that doesn't work, and I can't seem to make a Custom format work. Any ideas?

2. Ideally, I'd like to quickly convert these timecodes to hh:mm:ss:ff. Would macro work on this? So, I'd be taking 2:02 and making it 00:02:02:00. I think I need to solve #1 before I tackle #2, but i could be wrong.

Thanks in advance for any tips or ideas. I have about 1k rows...so to do this by hand would really stink.

how about if you format your times [hh]:mm:ss , does this help?

Unfortunately, that doesn't work. Seems like it should, but it doesn't.

Originally Posted by exceldoofus

Unfortunately, that doesn't work. Seems like it should, but it doesn't.

It should work for the first part of the question?

Assuming your data is in column A (cell A1 onwards), enter in cell B1:
=TIME(0,HOUR(A1),MINUTE(A1))
Format as h:mm:ss
and copy on.

I did not understand what FF is.


Mangesh

Originally Posted by mangesh_yadav

Assuming your data is in column A (cell A1 onwards), enter in cell B1:
=TIME(0,HOUR(A1),MINUTE(A1))
Format as h:mm:ss
and copy on.

I did not understand what FF is.


Mangesh

Hi Mangesh,

Shouldn't that be

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

The OP wanted his data to be read as mm:ss whereas excel will read it as hh:mm. So you force excel to read it as mm:ss by saying that the hh is actually mm and mm is actually ss.

I hope I am clear

Mangesh

Unfortunately, it didn't work. Here's a sample of the data.. You can see that the formula converted it to the wrong thing. FF stands for frames. So, what I'm wanting to do is take this data and make it hours then minutes then seconds then frames. Right now it's minutes and seconds. So...I want 0:10 to be 00:00:10:00. The frames will always be 00, b/c this time code data doesn't have frames. However, it's being imported into a system that requires frames data.

0:10 12:00 AM
2:36
4:29
6:36
8:37
10:05
12:34
14:46
17:11
19:28
21:11
23:28
25:49:00
27:32:00
30:36:00
32:42:00
33:40:00
35:46:00
38:02:00
41:38:00
43:33:00

The previous formula works, but you need to format it as I mentioned earlier to display poperly.

You could try the following formula:
="00:"&TEXT(HOUR(A1),"00")&":"&TEXT(MINUTE(A1),"00")&":00"

but this will treat the result as text. Also it will not work for
25:49:00
27:32:00
30:36:00
32:42:00
33:40:00
35:46:00
38:02:00
41:38:00
43:33:00
as I don't know 25:49:00 is hh:mm:ss or mm:ss

Mangesh

Genius! That worked.

The other tc's I have are 35:46:00 are mm:ss:ff, and I need those to be hh:mm:ss:ff or 00:35:46:00. I also have some that are like 0:53:25. Those would be h:mm:ss, and I need them to be hh:mm:ss:ff, so 00:53:25:00. Such inconsistency.

Thanks again. So smart!!

Hi,

This will work for all the data:

="00:"&TEXT(IF(DAY(A1)<1,HOUR(A1),IF(DAY(A1)<2,24+HOUR(A1),48+HOUR(A1))),"00")&":"&TEXT(MINUTE(A1),"00")&":00"


Mangesh