Skip to main content

Formula Hot Sheet

Formula HotSheet

I’ve been using Excel / Google Sheets since 2002 or so. This isn’t a breakdown of the basics, but rather, a place where I dump more complex formulas for reference later. 

Some Low-Hanging-Fruit

Here’s a formula that tells you what the date is today

=today()

Here’s a formula that calculates the days between two dates (where E5 is date 1, and E4 is date 2)

=DAYS(E5,E4)

Here’s a that will concat a separate date & time field, and keep it formatted as you desire

=TEXT(B12, "M/DD/YYYY")&" "&TEXT(C12, "h:mmam/pm")

GoogleSheets-ConcatDates.png

Concatenating more than one date and/or time requires using the TEXT function, otherwise you’ll end up with numbers that don’t mean anything to you. 

Timecode

These formulas will take 00:00:00:00 formatted timecode 30fps or 60fps timecode and convert it to frame count (where cell B5 = timecode) or perform calculations or convert from frame count back to formatted timecode. Note, these only work for WHOLE FRAME timecode (eg, 23.976 won’t work).   

30fps
=SUM(SUM(SUM(MID(B5, 4,2)*60)+SUM(MID(B5,7,2)*1))*30,N("FPS"))+SUM(RIGHT(B5,2)*1)

Where B2 = FPS cell and A2:A is a range of timecode to add - 

=SUMPRODUCT(IFERROR(SPLIT(A2:A,":")*{B2*3600,B2*60,B2,1}))
60fps
=SUM(SUM(SUM(MID(B5, 4,2)*60)+SUM(MID(B5,7,2)*1))*60,N("FPS"))+SUM(RIGHT(B5,2)*1)

Where B2 = FPS cell and A2:A is a range of timecode to add - 

=SUMPRODUCT(IFERROR(SPLIT(A2:A,":")*{B2*3600,B2*60,B2,1}))
Converting Frame Count Duration (eg "156")

Into formatted timecode. Where C2 = total frame count and B2 = FPS

=TEXT(TIME(0,0,C2/B2),"hh:mm:ss") &":"& TEXT(MOD(C2,B2),"00")
Performing a Timecode Calculation 

from formatted timecode (math happens over frame counts), then converting it back to proper timecode format. Where B2 = FPS, A2:A = Range

=TEXT(TIME(0,0,SUMPRODUCT(
    IFERROR(SPLIT(A2:A,":")*{B2*3600,B2*60,B2,1})
  )/B2),"hh:mm:ss")
  &":"&TEXT(MOD(SUMPRODUCT(RIGHT(A2:A,2)),B2),"00")

Automatic Population Based on IF

Here’s a formula that will automatically populate a cell with whatever’s furthest to the right in a column (from column C through column ZZ, row 4)

=ArrayFormula((IFERROR(LOOKUP( 2, 1 / ( C4:ZZ4 <> "" ), C4:ZZ4 ),"")))

Here’s a formula that will automatically populate a cell with a most recent date from a header row if the array’s data row contains text. The 5th row is the date row. The 7th row is the data row. 

=ArrayFormula(IFERROR(LOOKUP(2,1/(G7:Z7>0),$G$5:$Z$5),"None"))


 

Back to top