Skip to content

Instantly share code, notes, and snippets.

@Ryderpro

Ryderpro/sheetformulas.md

Last active August 23, 2020 21:11
Show Gist options
  • Save Ryderpro/657cb0cbbb3a1a1cdfbc04e66d045865 to your computer and use it in GitHub Desktop.
Save Ryderpro/657cb0cbbb3a1a1cdfbc04e66d045865 to your computer and use it in GitHub Desktop.
Download ZIP
Helpful Google Sheet Formulas
Raw
sheetformulas.md

Helpful Google Sheet Formulas

Convert ISO DateTime into date value

A1 = "2019-01-01T00:20:00"
=datevalue(left(A1,10))+TIMEVALUE(mid(A1,12,8))
returns 43466.01389

Convert ISO DateTime into date with inherited cell format

A1 = "2019-01-01T00:20:00"
=TO_DATE(datevalue(left(a1,10))+TIMEVALUE(mid(a1,12,8)))
returns 1/1/2019

Query data for count of records grouped by J pivot on Month

=QUERY(data!A:N,"select J, count(J) where(J > 0) group by J pivot Month(N)+1")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment