We needed to be able to carry out calculations within Excel on times which included a frames element (eg in the format hh:mm:s:ff)

Excel didn't seem to have a ready made time format or formula to deal with this so I was asked to come up with some functions to do the job.

Function to calculate the number of Frames from a Time (h:mm:ss:ff)

Function frames(c As Variant) As Variant
Dim f As Single

'Frames per second
f = 25

frames = (Left(c, 1) * (f * 60 * 60)) + (Mid(c, 3, 2) * (f * 60)) + (Mid(c, 6, 2) * f) + (Right(c, 2) * 1)
End Function

Function to calculate the number of Frames from a Time (hh:mm:ss:ff)

Function frames2(c As Variant) As Variant
Dim f As Single

'Frames per second
f = 25

frames2 = (Left(c, 2) * (f * 60 * 60)) + (Mid(c, 4, 2) * (f * 60)) + (Mid(c, 7, 2) * f) + (Right(c, 2) * 1)
End Function

A Function to calculate the Time from the number of Frames

Function timef(c As Variant) As Variant
Dim x As Long
Dim x1 As Long
Dim x2 As Long
Dim x3 As Long
Dim x4 As Long
Dim x5 As Long
Dim x6 As Long
Dim x7 As Variant
Dim f As Single
Dim y As Variant
Dim y1 As Variant
Dim y2 As Variant
Dim y3 As Variant
Dim y4 As Variant

'Frames per second
f = 25

x = WorksheetFunction.RoundDown(((c / (f * 60 * 60))), 0)
x1 = c - (x * (f * 60 * 60))
x2 = WorksheetFunction.RoundDown((x1 / (f * 60)), 0)
x3 = x1 - (x2 * (f * 60))
x4 = WorksheetFunction.RoundDown((x3 / f), 0)
x5 = x3 - (x4 * f)
x6 = x5

y = WorksheetFunction.Text(x, "00")
y1 = WorksheetFunction.Text(x2, "00")
y2 = WorksheetFunction.Text(x4, "00")
y3 = WorksheetFunction.Text(x6, "00")
y4 = y & ":" & y1 & ":" & y2 & ":" & y3

timef = y4
End Function

Download: Frames.bas