Bug in modified Timecode Calculator *need help*

**howiechin** · 05-12-2015, 02:11 PM

Hi forum

I downloaded an excel file called TC.xlsx and did a little modification to it.

I'm trying to do a subtraction based on the total time I've used.
For example in this attached excel sheet, I'm given 30minutes of airtime.

Based on my TC in and TC out, I wanted to use the total duration and minus 30 minutes from it.

Problem started when I try to convert the cell data as text. I successfully converted it to HH:MM:SS but the FF(frames) doesn't go through properly.

I've color coded them for clarity -

green are the durations for the videos
orange is total duration (added off the green)
Blue is the one I'm trying to convert to text so that it can be subtracted from the 30minutes (allocated time)

Its the blue cell that I'm having problem with.

Hopefully someone can shine some light thru!

Thanks in advance!!...this will save a lot of people. If this works, I will start spreading this to the video editing community.

**shg** · 05-12-2015, 03:11 PM

Maybe ...

Row\Col	A	B	C	D
1			fps
2			25
3			Duration
4	TC In	TC Out	hh:mm:ss:ff
5	00:00:03:10	00:00:07:18	00:00:04:08	C5: = TEXT(LEFT(B5,8) - LEFT(A5,8) - (RIGHT(A5,2) > RIGHT(B5,2)) / 86401, "hh:mm:ss" ) & TEXT(MOD(RIGHT(B5,2) - RIGHT(A5,2), $C$2), "\:00")
6	00:00:14:21	00:00:20:03	00:00:05:07
7	00:00:01:00	00:00:30:20	00:00:29:20
8	0	0	00:00:00:00
9	0	0	00:00:00:00
10	0	0	00:00:00:00
11	0	0	00:00:00:00
12	0	0	00:00:00:00
13	0	0	00:00:00:00
14	0	0	00:00:00:00
15			00:00:39:10	C15: =TEXT(SUMPRODUCT(LEFT(C5:C14, 8) + MOD(RIGHT(C5:C14, 2), $C$2)/$C$2/86401), "hh:mm:ss") & TEXT(MOD(SUMPRODUCT(--RIGHT(C5:C14, 2)), $C$2), "\:00")
16
17
18		Dur hh:mm:ss.00	00:00:39.40	C18: =LEFT(C15, 8) + RIGHT(C15, 2)/$C$2/86401
19		Allocated	00:30:00.00	C19: Input
20		Remaining	00:29:20.60	C20: =C19-C18

**howiechin** · 05-16-2015, 08:54 PM

Hi Shg, This sorta worked, but the addition part is not accurate.

Time for some weird case...

I found a bug in the original sheet that I downloaded from here...When i input 00:00:11:00, at the total, it adds as 00:00:15:00. However, when I input 00:00:11:01, it adds up correctly...I have no idea where the extra 4 seconds came from and its really weird.

File attached

Thanks you all so much in advance!!!

Bug in modified Timecode Calculator need help

LinkBack

Thread Tools

Rate This Thread

Display