ABCDEFGHIJKLMNOPQR
1

You must install and enable the following add-on in the spreadsheets where you want these wonderful timecode custom functions to be available:
2
TimeCode-MagiKs Google Sheet add-on: https://workspace.google.com/u/0/marketplace/app/timecodemagiks/503934260838
3
(For now only integer frame rates are supported)
4
TimeCode-MagiKs version :v1.4(If "Loading..." or "#NAME?" is displayed on the left of this cell, (re)open the spreadsheet and hit refresh - This is a Google bug)
5
Blue cells = input data
6
Green Cells = cells containing a formula
7
Yellow cells = cells containg more results from the cells in green
8
24 FPS time codes: 01:32:24:18
02:42:34:07		1:0002:09:40:0701:29:39:1001:33:41:0302:00:08:1101:29:19:1501:28:49:1702:36:22:1101:14:55:18
9
Available functions with usage examples as used in column B

Each of the functions below is applied on a range, they can also be used on a specific cell or literal value

(Click each cell in green to checkout their formula)
10
TC_CONV(B5:T5, 24, 25) :
(24 to 25 conversion)		01:28:42:24
02:36:04:03		00:00:00:2402:04:29:0201:26:04:0601:29:56:0701:55:20:0301:25:45:0601:25:16:1302:30:07:0401:11:55:23
11
TC_TO_FRAME(B5:T5, 24) :
(Convert a range of timecodes at 24 fps to frame number)		133074
234103		24186727129106134907173003128631127913225179107898
12
TC_TO_FRAME(B7:T7, 25) :
(Convert a range of timecodes at 25 fps to frame number)		133074
234103		24186727129106134907173003128631127913225179107898
13
FRAME_TO_TC(B9:T9, 24) :
(Convert a frame number to its 24 fps timecode representation)		01:32:24:18
02:42:34:07		00:00:01:0002:09:40:0701:29:39:1001:33:41:0302:00:08:1101:29:19:1501:28:49:1702:36:22:1101:14:55:18
14
TC_OFFSET(B10:T10, -1 * TC_TO_FRAME("2:00",24), 24) :
(Apply a negative offset of 2 seconds to a range of 24 fps timecode)		01:32:22:18
02:42:32:07		23:59:59:0002:09:38:0701:29:37:1001:33:39:0302:00:06:1101:29:17:1501:28:47:1702:36:20:1101:14:53:18
15
HFROMI($B$9:$9, 24) :
(Get the integer number of hours from a range of frame numbers at 24fps)		1
2		021121121
16
MFROMI($B$9:$9, 24) :
(Get the integer number of minutes from a range of frame numbers at 24fps)		32
42		092933029283614
17
SFROMI($B$9:$9, 24) :
(Get the integer number of seconds from a range of frame numbers at 24fps)		24
34		1403941819492255
18
IFROMI($B$9:$9, 24) :
(Get the integer number of image from a range of frame numbers at 24fps)		18
7		071031115171118
19
MFROMIRAW($B$9:$9, 24) :
(Get the total integer number of minutes from a range of frame numbers at 24fps)		92
162		01298993120898815674
20
21
22
23
Function to work with EDLs imported via the 'import EDL' in the Add-ons menu:
In the folowing examples, we use the EDL imported in the EDLExample sheet
24
TC_MATCHBACK(timeCode, fps, a_edl, offset)
Example:
TC_MATCHBACK("10:01:02:03", 24, sheet3!A3:H, 0)

Summary:
Use an EDL to get a record timeCode's related source timeCode. (most effects are not supported yet.)

timeCode:
The record timeCode for wich you want the related source timeCode (Can be a range)

fps:
The frame rate of the provided record timeCode and EDL

a_edl:
The EDL represented by a range containing rows of 8 cells each [Event#, Source, Track, EventType, TCSourceIn, TCSourceOut, TCRecordIn, TCRecordOut]

offset:
An optional offset to apply to the returned TC
25
Exemple :

TC_MATCHBACK("01:02:03:04", 24, EDLExample!A3:H)

"get the time code of the source edited at 01:02:03:04 and 01:02:03:05"

(using a multiline input in this example to show that it's supported but it's best to provide a range of multiple cells)

When source timecodes are not unique throughout the timeline the source name and the event number are added with the returned timecode
If there is a Dissolve at this record location boths source timecodes are returned		07:02:18:00 (s: bob107) (e: 240)
07:02:18:01 (s: bob107) (e: 240)		01:02:03:04
01:02:03:05
26
TC_REVERSE_MATCHBACK(timeCode, fps, a_edl, offset)Example:

TC_REVERSE_MATCHBACK("10:01:02:03", 24, sheet3!A3:H, 0)

Summary:

Use an EDL to get a source timeCode's related record timeCode. (most effects are not supported yet.) @author wej

timeCode:

The source timeCode for wich you want the related record timeCode (Can be a range)

fps:

The frame rate of the provided source timeCode and EDL

a_edl:

The EDL represented by a range containing rows of 8 cells each [Event#, Source, Track, EventType, TCSourceIn, TCSourceOut, TCRecordIn, TCRecordOut]

offset:

An optional offset to apply to the returned TC
27
Exemple :

TC_REVERSE_MATCHBACK("01:02:03:04", 24, EDLExample!A3:H)

"where is the source timecodes 07:02:18:00 and 07:02:18:01 edited ?"

(as a multiline input in this example to show that it's supported but it's best to provide it as an array if possible else it can get confusing when multiple values are returned for each input...)


When source timecodes are not unique throughout the timeline the source name and the event number are added with the returned timecode.
Additionaly if this source timecode is edited at several locations, all the record, source and event numbers are returned		01:02:03:04 (s: bob107) (e: 240)
01:02:03:05 (s: bob107) (e: 240)
28
EDL_SUMMARY(a_edl, fps)
Example:

EDL_SUMMARY(sheet3!A3:H, 24)

Summary:

Returns various useful details avout an EDL (Most effects are not supported yet.)

a_edl:

The EDL represented by a range containing rows of 8 cells each [Event#, Source, Track, EventType, TCSourceIn, TCSourceOut, TCRecordIn, TCRecordOut]

fps:

The frame rate of the provided record timeCode and EDL
29
30
Example :

EDL_SUMMARY(EDLExample!A3:H,24)		=== EDL Summary (v1.4) ===
31
Events:451(*)
32
Edited source length:16340401:53:28:12
33
Edit length:16340401:53:28:12
34
Edit real duration:01h 53m 28.5s
35
Number of sources:14
(source timecodes are NOT unique: 1 overlaps detected)
36
---- Edited length per source ----
37
Source nameFrame #Duration
38
START48800:00:20:08
39
bob1011288500:08:56:21
40
bob1021424100:09:53:09
41
bob1031467900:10:11:15
42
bob1041422000:09:52:12
43
bob1051466100:10:10:21
44
bob1061487000:10:19:14
45
bob1071481600:10:17:08
46
bob1081339600:09:18:04
47
bob1091501000:10:25:10
48
bob1101344800:09:20:08
49
bob1111305300:09:03:21
50
bob112739700:05:08:05
51
END24000:00:10:00
52
=== End of summary ===
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100