CODEWITHSUNDEEP
vbaexcel
user3000617
user3000617

Reputation: 13

Writing a function from a formula

I'm trying to convert the following formula to a function in excel:

=REPLACE(REPLACE(R1C1,10,3,(ROUND((RIGHT(R1C1,3)*29.97)/1000,0))),9,1,"";"")

What this does is take a timecode like this: 00:01:35,748 and turn it into this: 00:01:35;22

I don't know how to put the existing functions into a custom function and what the result would look like.

Upvotes: 1

Views: 111

Answers (1)

Floris
Floris

Reputation: 46435

Open the VBA editor (Alt-F11), select "insert module" from the Insert menu, then type the following (updated for better code practices - thanks @ja72 for inputs):

Option Explicit
Public Const fps = 29.97 ' frames per second

Function timeCode(s)
' take a string as input
' find the fractional time
' and convert to frame number like 00:01:35,748
' and turn it into this: 00:01:35;22

Dim comma, fraction, frameNum, frameInSec

comma = InStr(1, s, ",")
fraction = Mid(s, comma + 1, Len(s))
' convert to seconds regardless of number of digits:
frameInSec = Val(fraction) / (10 ^ Len(fraction))
frameNum = Application.WorksheetFunction.Round(frameInSec * fps, 0)
timeCode = Left(s, comma - 1) & ";" & frameNum

End Function

Now you will be able to enter a formula like

=timeCode("00:01:35,748")

into your spreadsheet, and the result will be

00:01:35;22

Which is what you wanted...

Instead of the string, you can of course use a cell reference - so if you have "00:01:35,748" in cell A1, you can type

=timeCode(A1)

in cell B1 (for example) and get the result you want. Custom functions - powerful stuff.

EDIT by special request (two for the price of one...) : changing the formatting out the output string to replace : with ;, and make the frame number always two digits (with leading zero if needed):

Dim tempString
tempString = Left(s, comma - 1) & ";" & Format(frameNum, "00")
timeCode = Replace(tempString, ":", ";")

It should be obvious where you put this in the above code...

Upvotes: 2

Related Questions