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")

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"))

Conditional Formatting

Oddly, you need to apply absolute cell references (either column or row, depending on formula goal) if you want conditional formatting to copy and paste and stay relational, you apply the absolute technique. This will keep things relational in a conditional formatting copy/paste. 

This will apply conditional formatting if the referenced cell (A1) contains the word “Cat”. Literally, if A1 contains “Cat”, then toggle on, else toggle off: 

=if(regexmatch($A1,"Cat"),1,0)

This will apply conditional formatting if the referenced cell (A1) contains the word “someword” where “someword” is case sensitive

=if(REGEXMATCH($A1,"(?i).*someword.*"),1,0)

This will apply conditional formatting in a cell if the reference cell is checked (using the inserted checkbox function). In this example, this applied in cell E7. So if D7 is checked, E7 will follow the conditional formatting rule. 

=IF($D7,1,0)

This will apply conditional formatting in a cell if the reference cell is checked (using the inserted checkbox function). In this example, this applied in cell E7. So if D7 is checked, E7 will follow the conditional formatting rule. 

=IF($D7,1,0)

This will do the exact same thing, with different syntax:

=$D7=TRUE

This set of conditional format will apply based on the number of days ago, where $E7 represents the target cell (self). This conditional format can be copied and applied to a range without changing the formula. The first time you put it in it needs to refer to the correct cell, but then will work from there.

Applies conditional formatting if the current cell date is between 0 and 6 days ago 

​​=IF((((DAYS($E7, TODAY()))>=-6)*((DAYS($E7, TODAY()))<=0))=1,1,0)

Applies conditional formatting if the current cell date is between 7 and 13 days ago

=IF((((DAYS($E7, TODAY()))>=-13)*((DAYS($E7, TODAY()))<=-7))=1,1,0)

Applies conditional formatting if the current cell date is between 14 and 20 days ago

=IF((((DAYS($E7, TODAY()))>=-20)*((DAYS($E7, TODAY()))<=-14))=1,1,0)

Applies conditional formatting if the current cell date is more than 20 days ago

=IF(DAYS($E7, TODAY())<-21,1,0)

This will apply conditional formatting to the most recent date as a header row. The first cell reference here has to be the first cell in the range (eg G5) and it applies to all of row 5. 

=G5=MAX($5:$5)