Timecode calculations

[Ferdie]

How does one obtain time code calculation in a Word table? I have been
logging footage where the descriptions are more important; thus Word's
grammar and spellchecker is preferable over Excel's features. I need
to determine the duration of the clips within the Word table (if
possible). Here's a short explanation.

Non-drop time code, NTSC

10(hh):01(mm):01(ss):01(ff).1(field)

1 hour = 60 minutes
1 minute = 60 seconds
1 second = 30 frame

Mark IN - Mark OUT = Duration
10:01:01:01.1 10:01:20:15.2 00:00:19:14.1

 

[Harry Chickpea]

How does one obtain time code calculation in a Word table? I have been
logging footage where the descriptions are more important; thus Word's
grammar and spellchecker is preferable over Excel's features. I need
to determine the duration of the clips within the Word table (if
possible). Here's a short explanation.

Non-drop time code, NTSC

10(hh):01(mm):01(ss):01(ff).1(field)

1 hour = 60 minutes
1 minute = 60 seconds
1 second = 30 frame

Mark IN - Mark OUT = Duration
10:01:01:01.1 10:01:20:15.2 00:00:19:14.1

Can't you just insert an Excel worksheet?

 

[Doug Robbins - Word MVP]

Hi Ferdie,

The following macro sums hh:mm:ss in the cells above the cell in which the
selection is located and enters the total in the cell with the selection:

“macro to sum time intervals



Dim Secs As Long, i As Long, s As String, m As String, h As String, myrange
As Range, rows As Long, col As Long, mytable As Table

On Error Resume Next
rows = Selection.Information(wdEndOfRangeRowNumber) - 1
col = Selection.Information(wdEndOfRangeColumnNumber)
Set mytable = Selection.Tables(1)
Secs = 0
For i = 2 To rows
Set myrange = mytable.Cell(i, col).Range
myrange.End = myrange.End - 1
Secs = Secs + Val(Right(myrange, 2)) + 60 * Val(Mid(myrange,
InStr(myrange, ":") + 1, 2)) + Val(Left(myrange, InStr(myrange, ":") - 1)) *
3600
Next i
s = Format(Secs Mod 60, "00")
m = Format(Int(Secs / 60) Mod 60, "00")
h = Format(Int(Secs / 3600), "0")
Selection.Text = h & ":" & m & ":" & s
Selection.Collapse wdCollapseEnd

You will need to modify it to account for the presence of the ff.field in
the cells. If the time part is always entered in the format hh:mm:ss and
never h:mm:ss or hh:m:ss etc. then use

Secs = Secs + Val(Mid(myrange, 7, 2)) + 60 * Val(Mid(myrange, 4, 2)) +
Val(Left(myrange, 2) - 1)) * 3600
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ferdie]

On Tue, 6 Apr 2004 08:03:23 +1000, "Doug Robbins - Word MVP"

David,

Your code is a fantastic start. I made some minor adjustments for my
readability. Let me know if you could explain a few points.
Basically, your code converts everything into seconds then converts it
back to time. So I would have to convert everything into frames;
subtract the number of frames; then convert back to time (Correct)?

1 hour = 108000 frames
1 minute = 1800 frames
1 second = 30 frames

Second, I am less familiar with iterating through a table. The concept
seems somewhat foreign to me. This table is not like a typical
spreadsheet. It has numerous merge cells and a header and footer for
each table section. Basically, the main body looks like below.
--------------------------------------------------------------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION |
--------------------------------------------------------------------------------------------------------

I was hoping to create a formula like R2C3 = R2C5 - R2C6; after
convert to frames (blah blah). I only need the duration of the clip;
not the entire duration. Finally, any ideas on starting the macro or
building a formula-like event? Or Does one select Tool > Macros > Run.
Thanks for leading in the right direction. Your assistance has been
encouraging.

-Ferdie

Dim Secs As Long
Dim i As Long
Dim str As String
Dim strMin As String
Dim strHour As String
Dim myRange As Range
Dim lngRows As Long
Dim lngCol As Long
Dim myTable As Table

On Error Resume Next

lngRows = Selection.Information(wdEndOfRangeRowNumber) - 1
lngCol = Selection.Information(wdEndOfRangeColumnNumber)

Set myTable = Selection.Tables(1)
Secs = 0

For i = 2 To Rows
Set myRange = myTable.Cell(i, lngCol).Range
myRange.End = myRange.End - 1
strSecs = strSecs + Val(Right(myRange, 2)) + 60 * Val(Mid(myRange,
InStr(myRange, ":") + 1, 2)) _
+ Val(Left(myRange, InStr(myRange, ":") - 1)) * 3600
Next i

strSec = Format(strSecs Mod 60, "00")
strMin = Format(Int(strSecs / 60) Mod 60, "00")
strHour = Format(Int(strSecs / 3600), "0")
Selection.Text =strHour & ":" & strMin & ":" & strSec
Selection.Collapse wdCollapseEnd

' You will need to modify it to account for the presence of the
ff.field in
' the cells. If the time part is always entered in the format
hh:mm:ss and
' never h:mm:ss or hh:m:ss etc. then use

strSecs = strSecs + Val(Mid(myRange, 7, 2)) + 60 * Val(Mid(myRange, 4,
2)) _
+ Val(Left(myRange, 2) - 1) * 3600

 

[Doug Robbins - Word MVP]

Hi Ferdie,

In what column of the table

--------------------------------------------------------------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION |
--------------------------------------------------------------------------------------------------------

does the duration get inserted. I understand that you want to get the
duration of each record by subtracting MARK IN from MARK OUT.

You are not going to be able to do this by means of a formula. It is
possible to do it using a macro that could either run through the rows of
the table working out the duration for each row in which case you would have
to call it using Tools>Macro>Macros, or if you set the table up as a form
using formfields in a protected document, the macro could be made to run
automatically on exit from a field.

If it were me however, I would use an Access Database for this.

Alternatively, as ChickPea suggested, insert an Excel Spreadsheet in the
document instead of a table. Other than when you are actually working in
the table, it can look the same as a Word table (e.g. when printed)

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[macropod]

Hi Ferdie,

Contrary to what you've been told, Word can calculate times using field
formulae, but they're fairly complicated and you can't use table cell
references - you'd need to bookmark each time value instead.

For some examples of time calculations, download the Word document at:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
(url all one line)

Cheers
PS: The reason you can't use cell references is because Word adds the hours,
minutes and seconds together when referenced this way!

 

[Ferdie]

David,

Thanks for your response. The LENGTH column header represent the
duration (It was just shorter then duration header and I needed to
lessen the column width). The MARK IN and MARK OUT column header need
to be subtracted for LENGTH. As already mentioned, the table has a
header and footer. Each table represents a setup which includes Date,
Scene Heading, Scene Number, Camera Roll, Camera, and Additional
Notes. The previously mention header relate to each take.

-----------------------------------------------------------------------------------------------------------------------------
| DATE | TIME |
-----------------------------------------------------------------------------------------------------------------------------
| SCENE HEADING |
-----------------------------------------------------------------------------------------------------------------------------
| SCENE NUMBER |
-----------------------------------------------------------------------------------------------------------------------------
| SCRIPT DESCRIPTION |
-----------------------------------------------------------------------------------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION | Column Headers
------------------------------------------------------------------------------------------------------------------------------
| CAMERA ROLL | CAMERA |
------------------------------------------------------------------------------------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION |
------------------------------------------------------------------------------------------------------------------------------
| NOTES |
------------------------------------------------------------------------------------------------------------------------------

Eventually, I will create a database for this purpose. Unfortunately,
people on this project have used FileMaker, which I am less familiar.
In addition, I am on this project on a freelance basis and would be
happy to reinvent the wheel if they commission me. Yet I already have
ten Words documents which need these calculations. I am retrofitting
these tables without recreating the beast.

or if you set the table up as a form using form fields in a protected document,

This suggestion sounds interesting. What do you think in light of the
additional information?

Let me know if you have any additional ideas.

 

[Ferdie]

Macropod,

Your document with time calculations stands no comparison. You have
outstanding work here. Yet the document is somewhat over my head since
it contains so much information. For starter, it took me some time to
realize that you had to toggle the field codes a couple time to see
the whole process. Second, you discussed Julian and Gregorian dates in
length, yet the time codes have left me perplexed.

A formula for converting the time code into frames then back would
assist me. Do I even need to convert to frames? In addition, the
descriptions of the field codes needed would lead me in the right
direction. Basically, I have less familiarity with field codes and
find them harder to read than VBA. But I am willing to tame the beast.
Let me know if you aid me in this request.

-Ferdie

 

[macropod]

Hi Ferdie,

From what you've posted previously, it seems that the frame count is merely
the number of seconds multiplied by 30. That makes it fairly easy to convert
between the two (using the SumTime or TimeDiff parameters, depending on
which version of the field you're using) but, if you're only trying to
calculate the duration based on the 'mark in' & 'mark out' times, there's no
need to do such a conversion. The only time you'd need the conversion is if
you want to calculate how many frames are used over a given time, or how
long it would take to run a given number of frames.

Cheers
PS: To expand any of the fields in the document, select the whole field then
press Shift-F9 once. To expand all of the fields in the document, press
Alt-F9 once anywhere in the document.

 

[Doug Robbins - Word MVP]

Ferdie,

I'm not too clear on how the form is setup. Can you email me a typically
completed example?

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ferdie]

Macropod,

The main issue for me is a lack of familiarity with these field codes.
Searching help for details does not always aid me either. What are
SumTime and TimeDiff? How do you bookmark so you could refer to a
table cells? What part of your DateCal document should I focus on? The
basic Excel formulas for the converting to total number frames and
converting back to timecode are below.

I am still figuring out the formula part in Excel so please bear with
me. These formulas require three additional cells to subtract the
total number of frames; then convert frames back to time code. The
translation into Word fields is my next challenge. In essence, I need
to figure multiple aspects of this process and need help to get up to
speed.

Number of frames in timecode

NTSC
108000 the total number of frames in one hour, in NTSC,
1800 the total number of frames in one minute, in NTSC,
30 the total number of frames in one second, in NTSC

(RC represents a cell reference)

MID(TEXT(RC[-3],"00:00:00:00"),1,2)*108000
+MID(TEXT(RC[-3],"00:00:00:00"),4,2)*1800
+MID(TEXT(RC[-3],"00:00:00:00"),7,2)*30
+MID(TEXT(RC[-3],"00:00:00:00"),10,2)

TEXT(TRUNC(RC[3]/108000),"00:")
&TEXT(TRUNC(MOD(RC[3],108000)/1800),"00:")
&TEXT(TRUNC(MOD(RC[3],108000)/30),"00:")
&TEXT((MOD(RC[3],30)),"00")

 

[Ferdie]

David,

You will find a one page sample table in the email attachment. A
typical document has multiple tables and several pages long. This
document was built from an on-set form to keep a consistent format.
Altering the format is less desirable.

Here's a quick explanation of the shorthand used in the script notes.
The first line includes date/time. The scene heading refers to the
script heading. The scene number allows you to quickly locate the shot
within the script. The shot description details whether we shot the
scene indoors or outdoors (INT. or EXT.) and a quick description. The
column headings are self explanatory, yet SCENE refers to the slated
scene information, which often corresponds to the previz number.

At a glance, this form may confuse you more. Just let me know where
you need clarification. I will give you the best explanation possible.

 

[Doug Robbins - Word MVP]

Hi Ferdie,

IF you select the rows containing the cells for which you want the
calculation performed, and run the following macro, the "LENGTH" cell in
each row will be populated with the difference between the MARK IN and MARK
OUT in the format "hh:mm:ss:ff". I have ignored the .1 at teh end of each
entry in the MARK IN and MARK OUT cells.

Dim arange As range, rrange As range, markin As range, markout As range, i
As Long
Dim framein As Long, frameout As Long, numframes As Long
Dim lhours As String, lminutes As String, lseconds As String, lframes As
String
Set arange = Selection.range
For i = 1 To arange.rows.Count
Set markin = arange.rows(i).Cells(5).range
Set markout = arange.rows(i).Cells(6).range
framein = 108000 * Left(markin, 2) + 1800 * Mid(markin, 4, 2) + 30 *
Mid(markin, 7, 2) + Mid(markin, 10, 2)
frameout = 108000 * Left(markout, 2) + 1800 * Mid(markout, 4, 2) + 30 *
Mid(markout, 7, 2) + Mid(markout, 10, 2)
numframes = frameout - framein
lhours = Format(Int(numframes / 108000), "0#")
lminutes = Format(Int((numframes Mod 108000) / 1800), "0#")
lseconds = Format(Int((numframes Mod 1800) / 30), "0#")
lframes = Format(numframes Mod 30, "0#")
arange.rows(i).Cells(3).range = lhours & ":" & lminutes & ":" & lseconds
& ":" & lframes
Next i

If you just wanted the total number of frames, rather than the difference in
"hh:mm:ss:ff" format, replace the following lines of code:

lhours = Format(Int(numframes / 108000), "0#")
lminutes = Format(Int((numframes Mod 108000) / 1800), "0#")
lseconds = Format(Int((numframes Mod 1800) / 30), "0#")
lframes = Format(numframes Mod 30, "0#")
arange.rows(i).Cells(3).range = lhours & ":" & lminutes & ":" & lseconds
& ":" & lframes

with

arange.rows(i).Cells(3).range = numframes

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[macropod]

Hi Ferdie,

Re the questions raised in your last post:

Q: What are SumTime and TimeDiff?
A: These are field names (ie bookmarks) used in the time calculation
examples on the last page (ie numbered page 9) of the DateCalc document.

Q: How do you bookmark so you could refer to a table cells?
A: You could use Insert|Bookmark from the menu, or you could use a nested
QUOTE ASK and REF field in each of the cells used in the calculation (eg:
{Quote{ASK Time1 "What is the MarkIn Time?"}{MarkIn1}} in one cell, and
{Quote{ASK Time2 "What is the MarkOut Time?"}{MarkOut1}} in another cell).
This is easy enough to incorporate into a time calculation when you've only
got two cells to worry about, but you appear to have at least four, and you'
d probably need to change the bookmark names for each MarkIn/ MarkOut pair.

Q: What part of your DateCalc document should I focus on?
A: Numbered pages 1 & 9.

However, your timecode calculations require rather more than Word can
readily achieve using fields whilst maintaining your timecode input format.
For that reason, plus the fact that you'd need to use separate bookmarks for
each MarkIn/ MarkOut pair, I'd recommend embedding an Excel spreadsheet with
the required table structure and formulae.

Earlier, you posted the following table layout:

----------------------------------------------------------------------------
-------------------------------------------------
| DATE |
TIME |
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE HEADING
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE NUMBER
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCRIPT DESCRIPTION
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION | Column
Headers
----------------------------------------------------------------------------
-------------------------------------------------
| CAMERA ROLL |
CAMERA |
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | DESCRIPTION |
----------------------------------------------------------------------------
-------------------------------------------------
| NOTES
|
----------------------------------------------------------------------------
-------------------------------------------------

Taking that as a starting point, I've inserted a 'Frames' header before your
'Description' header to produce an embedded Excel spreadsheet as follows:

----------------------------------------------------------------------------
-------------------------------------------------
| DATE |
TIME |
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE HEADING
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE NUMBER
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCRIPT DESCRIPTION
|
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | FRAMES |
DESCRIPTION |
----------------------------------------------------------------------------
-------------------------------------------------
| | | | | | | |
----------------------------------------------------------------------------
-------------------------------------------------
| CAMERA ROLL |
CAMERA |
----------------------------------------------------------------------------
-------------------------------------------------
| SCENE | TAKE | LENGTH | LENS | MARK IN | MARK OUT | FRAMES |
DESCRIPTION |
----------------------------------------------------------------------------
-------------------------------------------------
| | | | | | | |
----------------------------------------------------------------------------
-------------------------------------------------| NOTES
|
----------------------------------------------------------------------------
-------------------------------------------------

Using your RC syntax, a formula that seems to give the required results for
the:

a) frames count (in Column 7) is:

=((MID(RC[-1],1,2)-MID(RC[-2],1,2))*3600+(MID(RC[-1],4,2)-MID(RC[-2],4,2))*6
0+(MID(RC[-1],7,2)-MID(RC[-2],7,2)))*30+(MID(RC[-1],10,4)-MID(RC[-2],10,4))

b) length (in Column 3) is:

=TEXT(MOD(INT(RC[4]/30/60/60),60),"00:")&TEXT(MOD(INT(RC[4]/30/60),60),"00:"
)&TEXT(MOD(INT(RC[4]/30),60),"00:")&TEXT(ROUND(MOD(RC[4],30),1),"0.0")

The important thing to note here is that the 'length' calculation is based
on the 'frames' calculation, though it would be possible to do a 'length'
calculation independently of the 'frames' calculation. If you need to place
the 'frames' calculation in another column, simply cut & paste it.

Macropod,

The main issue for me is a lack of familiarity with these field codes.
Searching help for details does not always aid me either. What are
SumTime and TimeDiff? How do you bookmark so you could refer to a
table cells? What part of your DateCal document should I focus on? The
basic Excel formulas for the converting to total number frames and
converting back to timecode are below.

I am still figuring out the formula part in Excel so please bear with
me. These formulas require three additional cells to subtract the
total number of frames; then convert frames back to time code. The
translation into Word fields is my next challenge. In essence, I need
to figure multiple aspects of this process and need help to get up to
speed.

Number of frames in timecode

NTSC
108000 the total number of frames in one hour, in NTSC,
1800 the total number of frames in one minute, in NTSC,
30 the total number of frames in one second, in NTSC

(RC represents a cell reference)

MID(TEXT(RC[-3],"00:00:00:00"),1,2)*108000
+MID(TEXT(RC[-3],"00:00:00:00"),4,2)*1800
+MID(TEXT(RC[-3],"00:00:00:00"),7,2)*30
+MID(TEXT(RC[-3],"00:00:00:00"),10,2)

TEXT(TRUNC(RC[3]/108000),"00:")
&TEXT(TRUNC(MOD(RC[3],108000)/1800),"00:")
&TEXT(TRUNC(MOD(RC[3],108000)/30),"00:")
&TEXT((MOD(RC[3],30)),"00")

Hi Ferdie,

From what you've posted previously, it seems that the frame count is merely
the number of seconds multiplied by 30. That makes it fairly easy to convert
between the two (using the SumTime or TimeDiff parameters, depending on
which version of the field you're using) but, if you're only trying to
calculate the duration based on the 'mark in' & 'mark out' times, there's no
need to do such a conversion. The only time you'd need the conversion is if
you want to calculate how many frames are used over a given time, or how
long it would take to run a given number of frames.

Cheers
PS: To expand any of the fields in the document, select the whole field then
press Shift-F9 once. To expand all of the fields in the document, press
Alt-F9 once anywhere in the document.

Macropod,

Your document with time calculations stands no comparison. You have
outstanding work here. Yet the document is somewhat over my head since
it contains so much information. For starter, it took me some time to
realize that you had to toggle the field codes a couple time to see
the whole process. Second, you discussed Julian and Gregorian dates in
length, yet the time codes have left me perplexed.

A formula for converting the time code into frames then back would
assist me. Do I even need to convert to frames? In addition, the
descriptions of the field codes needed would lead me in the right
direction. Basically, I have less familiarity with field codes and
find them harder to read than VBA. But I am willing to tame the beast.
Let me know if you aid me in this request.

-Ferdie


On Wed, 7 Apr 2004 17:18:23 +1000, "macropod"

Hi Ferdie,

Contrary to what you've been told, Word can calculate times using field
formulae, but they're fairly complicated and you can't use table cell
references - you'd need to bookmark each time value instead.

For some examples of time calculations, download the Word document at:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
(url all one line)

Cheers
PS: The reason you can't use cell references is because Word adds the hours,
minutes and seconds together when referenced this way!


On Tue, 6 Apr 2004 08:03:23 +1000, "Doug Robbins - Word MVP"

David,

Your code is a fantastic start. I made some minor adjustments for my
readability. Let me know if you could explain a few points.
Basically, your code converts everything into seconds then converts it
back to time. So I would have to convert everything into frames;
subtract the number of frames; then convert back to time (Correct)?

1 hour = 108000 frames
1 minute = 1800 frames
1 second = 30 frames

Second, I am less familiar with iterating through a table. The concept
seems somewhat foreign to me. This table is not like a typical
spreadsheet. It has numerous merge cells and a header and footer for
each table section. Basically, the main body looks like below.

------------------------------------------------------------------------

-
- -
-

------------------------------

I was hoping to create a formula like R2C3 = R2C5 - R2C6; after
convert to frames (blah blah). I only need the duration of the clip;
not the entire duration. Finally, any ideas on starting the macro or
building a formula-like event? Or Does one select Tool > Macros > Run.
Thanks for leading in the right direction. Your assistance has been
encouraging.

-Ferdie

Dim Secs As Long
Dim i As Long
Dim str As String
Dim strMin As String
Dim strHour As String
Dim myRange As Range
Dim lngRows As Long
Dim lngCol As Long
Dim myTable As Table

On Error Resume Next

lngRows = Selection.Information(wdEndOfRangeRowNumber) - 1
lngCol = Selection.Information(wdEndOfRangeColumnNumber)

Set myTable = Selection.Tables(1)
Secs = 0

For i = 2 To Rows
Set myRange = myTable.Cell(i, lngCol).Range
myRange.End = myRange.End - 1
strSecs = strSecs + Val(Right(myRange, 2)) + 60 * Val(Mid(myRange,
InStr(myRange, ":") + 1, 2)) _
+ Val(Left(myRange, InStr(myRange, ":") - 1)) * 3600
Next i

strSec = Format(strSecs Mod 60, "00")
strMin = Format(Int(strSecs / 60) Mod 60, "00")
strHour = Format(Int(strSecs / 3600), "0")
Selection.Text =strHour & ":" & strMin & ":" & strSec
Selection.Collapse wdCollapseEnd

' You will need to modify it to account for the presence of the
ff.field in
' the cells. If the time part is always entered in the format
hh:mm:ss and
' never h:mm:ss or hh:m:ss etc. then use

strSecs = strSecs + Val(Mid(myRange, 7, 2)) + 60 * Val(Mid(myRange, 4,
2)) _
+ Val(Left(myRange, 2) - 1) * 3600



Hi Ferdie,

The following macro sums hh:mm:ss in the cells above the cell in which
the
selection is located and enters the total in the cell with the selection:

"macro to sum time intervals



Dim Secs As Long, i As Long, s As String, m As String, h As String,
myrange
As Range, rows As Long, col As Long, mytable As Table

On Error Resume Next
rows = Selection.Information(wdEndOfRangeRowNumber) - 1
col = Selection.Information(wdEndOfRangeColumnNumber)
Set mytable = Selection.Tables(1)
Secs = 0
For i = 2 To rows
Set myrange = mytable.Cell(i, col).Range
myrange.End = myrange.End - 1
Secs = Secs + Val(Right(myrange, 2)) + 60 * Val(Mid(myrange,
InStr(myrange, ":") + 1, 2)) + Val(Left(myrange, InStr(myrange, ":") -
1)) *
3600
Next i
s = Format(Secs Mod 60, "00")
m = Format(Int(Secs / 60) Mod 60, "00")
h = Format(Int(Secs / 3600), "0")
Selection.Text = h & ":" & m & ":" & s
Selection.Collapse wdCollapseEnd

You will need to modify it to account for the presence of the

ff.field
in

the cells. If the time part is always entered in the format

hh:mm:ss
and

never h:mm:ss or hh:m:ss etc. then use

Secs = Secs + Val(Mid(myrange, 7, 2)) + 60 * Val(Mid(myrange,

4,
2))

+
Val(Left(myrange, 2) - 1)) * 3600

 

[Ferdie]

Hey Doug,

Your code looks great. Now, I am heading in the right direction.
Several questions still come to mind. You may view the revamped code
below.

Basically, the code should test for the header and footer rows with
Scene Descriptions (reference the emailed file as an example). A
simple If statement and the Cell Count serves this purpose. The code
tests whether the row has more the six columns before executing the
next code block. Then the code should test whether the column 5 or 6
cells are empty with the Lens function. I should probably include
more error handling as well.

Finally, I need to develop logic to process multiple tables and exit
gracefully at the document's end. You may find some inconsistency in
the code since it remains untested. I would like to get your comments
before proceeding with further development. Let me know what you
suggestions you may have for me.


Sub TimecodeLength2()

Dim MyRange As Range
Dim rrange As Range
Dim MarkIn As Range
Dim MarkOut As Range
Dim i As Long
Dim lngFrameIn As Long
Dim lngFrameOut As Long
Dim lngNumFrames As Long
Dim strHours As String
Dim strMinutes As String
Dim strSeconds As String
Dim strFrames As String

Set MyRange = Selection.Range

For i = 1 To MyRange.Rows.Count
If MyRange.Rows(i).Cells.Count > 6 Then
Set MarkIn = MyRange.Rows(i).Cells(5).Range
Set MarkOut = MyRange.Rows(i).Cells(6).Range
If Len(MarkIn) > 0 And Len(MarkOut) > 0 Then
lngFrameIn = 108000 * Left(MarkIn, 2) _
+ 1800 * Mid(MarkIn, 4, 2) _
+ 30 * Mid(MarkIn, 7, 2) _
+ Mid(MarkIn, 10, 2)
lngFrameOut = 108000 * Left(MarkOut, 2) _
+ 1800 * Mid(MarkOut, 4, 2) _
+ 30 * Mid(MarkOut, 7, 2) _
+ Mid(MarkOut, 10, 2)
lngNumFrames = lngFrameOut - lngFrameIn
strHours = Format(Int(lngNumFrames / 108000), "0#")
strMinutes = Format(Int((lngNumFrames Mod 108000) / 1800),
"0#")
strSeconds = Format(Int((lngNumFrames Mod 1800) / 30),
"0#")
strFrames = Format(lngNumFrames Mod 30, "0#")
MyRange.Rows(i).Cells(3).Range = strHours & ":" &
strMinutes & ":" & strSeconds & ":" & strFrames
End If
End If
Next i

End Sub

 

[Doug Robbins - Word MVP]

Hi Ferdie,

Creating a documnet with multiple tables by selecting all in the sample form
and pasting at the end of the document, running the following macro
processes each of the relevant tables. It decides what's relevant by
checking for the existence of MARK IN in the fifth cell of the fifth row.:

Dim atab As Table
Dim arange As range, rrange As range, markin As range, markout As range, i
As Long
Dim framein As Long, frameout As Long, numframes As Long
Dim lhours As String, lminutes As String, lseconds As String, lframes As
String
For Each atab In ActiveDocument.Tables
Set arange = atab.Cell(5, 5).range
arange.End = arange.End - 1
If arange = "MARK IN" Then
For i = 7 To atab.rows.Count - 1
Set markin = atab.rows(i).Cells(5).range
Set markout = atab.rows(i).Cells(6).range
framein = 108000 * Left(markin, 2) + 1800 * Mid(markin, 4, 2) +
30 * Mid(markin, 7, 2) + Mid(markin, 10, 2)
frameout = 108000 * Left(markout, 2) + 1800 * Mid(markout, 4, 2)
+ 30 * Mid(markout, 7, 2) + Mid(markout, 10, 2)
numframes = frameout - framein
lhours = Format(Int(numframes / 108000), "0#")
lminutes = Format(Int((numframes Mod 108000) / 1800), "0#")
lseconds = Format(Int((numframes Mod 1800) / 30), "0#")
lframes = Format(numframes Mod 30, "0#")
atab.rows(i).Cells(3).range = lhours & ":" & lminutes & ":" &
lseconds & ":" & lframes
Next i
End If
Next atab


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP