Custom Time Format - hh:mm:ss:ff

[Gareth Roberts]

Hi all,

Is it possible to create a custom time format, in particular one that uses
frames?

I know I could always use "hh:mm:ss.xx" i.e. decimal fractions of seconds
but it would simpler if I could use and add, subtract "hh:mm:ss.ff" where ff
is 00 - 25, or indeed 00 - 30 for NTSC users.

Just thought someone else might already have tackled this...

Thanks,
Gareth
Win NT / Excel 97 SR-2

[Wade]

Here is something i just finished. It is designed to re-calculate timecode
based on an offset. Let's say you had 3 songs ,and you already programed
your cues. That's when someone decides that the middle song should be
removed. If the removed song used 00:03:30:28 of SMPTE, then that would be
your offset. This little do-dad would bump your cues timecode up. This is a
generic test version.
A2 = your old timecode in format HH:MM:SS:FF
B2= where A2 converted to decimal will be dumped
C2= Where the converted SMPTE will be dumped
D2= Your SMPTE offset in format HH:MM:SS:FF
E2= where D2 converted to decimal will be dumped
This is based on 30 frame no-drop

Code starts here>>

Option Explicit
Public Interval As String
Public CodeConvert As String

Sub MainControl()
Dim second As Integer
Dim offset As String
Dim newtime As Integer
Dim newFormat As Variant
Dim OldCode As String
Dim OldDec As Variant
Dim OffsetDec As Variant

OldCode = Sheets(1).Range("A2").Value
Call ConvertToDecimal(OldCode)
Sheets(1).Range("B2").Value = CodeConvert
OldDec = CDec(CodeConvert)

offset = Sheets(1).Range("d2").Value
Call ConvertToDecimal(offset)
Sheets(1).Range("E2").Value = CodeConvert
OffsetDec = CDec(CodeConvert)

newFormat = OldDec - OffsetDec
Call ConvertToSMPTE(newFormat)

Sheets(1).Range("C2").Value = Interval
End Sub

Function ConvertToSMPTE(newFormat As Variant) As String
Dim Frames As Long, Hours As Long, Minutes As Long, Seconds As Long
'there are 86400 seconds/day
'there are 2592000 frames/day

Frames = CLng(newFormat * 2592000)
Seconds = Frames \ 30
Frames = Frames Mod 30
Minutes = Seconds \ 60
Seconds = Seconds Mod 60
Hours = Minutes \ 60
Minutes = Minutes Mod 60
Hours = Hours Mod 24

Interval = Format$(Hours, "00") & ":" & Format$(Minutes, "00") & ":" &
Format$(Seconds, "00") & ":" & Format$(Frames, "00")

End Function

Function ConvertToDecimal(TimeIn As String) As String

Dim Count As Integer
Dim Mark As Integer
Dim midMark As Integer
Dim hour As Long
Dim min As Long
Dim sec As Long
Dim frame As Long
Dim total As Variant

Count = Len(TimeIn)
Mark = InStr(1, TimeIn, ":", vbTextCompare)
hour = Left(TimeIn, Count - (Count - Mark) - 1)
midMark = InStr(Mark + 1, TimeIn, ":", vbTextCompare)
min = Mid(TimeIn, Mark + 1, (midMark - Mark) - 1)
Mark = midMark
midMark = InStr(Mark + 1, TimeIn, ":", vbTextCompare)
sec = Mid(TimeIn, Mark + 1, (midMark - Mark) - 1)
frame = Right(TimeIn, Count - midMark)

total = CDec((((hour * 60 + min) * 60 + sec) * 30 + frame) / 2592000)
CodeConvert = total

Debug.Print total
End Function

>>Hope This Helps
>>Wade

[Gareth Roberts]

As easy as that huh!! <g>

Thanks a lot Wade - appreciate it. I shall have a tinker.
Bests,
Gareth

"Wade" <W...@anon.com> wrote in message news:ukprBTLfBHA.1368@tkmsftngp05...