Tjyylli

Unlike the other questions answered, I haven't seen my problem addressed. I work in TV and have time codes that need to be evaluated/stored.
01:23:45:04 is 1 hour, 23 mins, 45 secs and 4 frames (out of 24 or 30 depending on stuff).
When I concatenate some separate values from Excel cells together into a timecode, Excel ignores leading 00's all over the place, and won't allow my format. It likes days:hrs:mm:ss:partial secs displayed in 000 ths. etc.... I need to be able to combine 4 different 2 digit numbers into an 8 digit time code separated by colons (:) exactly like this: 01:43:23:02 and not have Excel give me 1:43:23:2.

Do you mean you have hours, minutes, seconds and frames in different cells? If you have those in A2, B2, C2 and D2 respectively then try using TEXT function to concatenate and retain leading zeroes, i.e.

=TEXT(A2,"00:")&TEXT(B2,"00:")&TEXT(C2,"00:")&TEXT(D2,"00")

If you could live with the frame being a decimal this might work:

hh:mm:ss.00

Otherwise I think you need to have the frame in a separate cell since it doesn't fit in with normal datetime stamps.

If you only want to store them, it is enough to format the cell. If you are going to use these data, you would have to use another method.

Only store:

1- Select the range of cells you want to format

2- Enter Start-Number-Format-Customize

3- Write on type: 00:00:00:00

From that moment the cell will adapt the data entered to the format 00:00:00:00

If you enter 123 the cell will show 00:00:01:23

If you enter 1234567 the cell will show 01:23:45:67

Attention! If you click on a formatted cell with data you will see that the format disappears. This is because what we have done is simply force the cell to a kind of graphic representation.

A cell in text mode would be the right thing to store this type of data.
To do addition and subtraction of TC this method does not work because a TC is a sexagesimal system and excel uses the decimal. Nor is it useful to format the cell in TIME mode.

There is another way and it is to use a text string to store a TC in the format 00:00:00:00

When you want to add two TC's you have to pass everything to frames add or subtract those two values and convert again to TC code.
Since the data is a string, first remove the colon from the TC with the replace statement:

numToConvert = Replace (numToConvert, ":", "")

The function to convert to seconds would be something like this:

Dim fra2s, seg2s, min2s, hor2s As Double

fra2s = 0

seg2s = 0

min2s = 0

hor2s = 0

fra2s = Mid (numToConvert, 7, 2) / framesRef

sec2s = Mid (numToConvert, 5, 2)

min2s = Mid (numToConvert, 3, 2) * 60

hor2s = Mid (numToConvert, 1, 2) * 3600

TcInSeconds = hor2s + min2s + seg2s + fra2s

where framesRef is the reference system (24,25,29.97 ...)

Now we add or subtract the two values and re-calculate the TC in its correct format. The function would be something like this:

Dim s2fra, s2sec, s2min, s2hor, TimeCodeTMP As Double

TimeCodeTMP = numToConvertInSeconds

s2fra = 0

s2seg = 0

s2min = 0

s2hor = 0

TimeCodeTMP = 0



'The "" or "MOD" operators can not be used because VBA Excel returns integer values and we need decimals

s2hor = Int (TimeCodeTMP / 3600)

TimeCodeTMP = TimeCodeTMP - (3600 * s2hor)

s2min = Int (TimeCodeTMP / 60)

TimeCodeTMP = TimeCodeTMP - (60 * s2min)

s2seg = Int (TimeCodeTMP)

TimeCodeTMP = TimeCodeTMP - (s2seg)

s2fra = Int (Round (TimeCodeTMP * framesRef, 0))

HHMMSSFF = Right ("00" & s2hor, 2) & ":" & Right ("00" & s2min, 2) & ":" & Right ("00" & s2sec, 2) & ":" & Right ("00"& s2fra, 2)

The last line is responsible for adding a zero to the number under 10. Example: 1:5:42:7 becomes 01:05:42:07 in string format.