Help

Convert Google Analytics Hour of Day

Topic Labels: Formulas
Solved
Jump to Solution
1213 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Anna_Allingham
4 - Data Explorer
4 - Data Explorer

I have imported our Google Analytics data through AirFlow and would like to convert the Hour of Day to the correct format. The way GA has it formatted is “2021070100”, which is 7/1/2021 at 12:00 AM. Is there a formula I can use to get the desired outcome? Thanks!

1 Solution

Accepted Solutions
Cameron_Kaiser
6 - Interface Innovator
6 - Interface Innovator

Formula: DATETIME_PARSE({Time Field}, “YYYYMMDDHH”)
The way it works is that you’re telling the parser your data is in the format “year month day hour”. Be mindful of the repeating letters, because they indicate the length of each unit. For example, the YYMDHH format of your date would be 217100 (same date, different format).

A list of Airtable’s date unit shorthands can be found here: Supported format specifiers for DATETIME_FORMAT – Airtable Support

See Solution in Thread

2 Replies 2
Cameron_Kaiser
6 - Interface Innovator
6 - Interface Innovator

Formula: DATETIME_PARSE({Time Field}, “YYYYMMDDHH”)
The way it works is that you’re telling the parser your data is in the format “year month day hour”. Be mindful of the repeating letters, because they indicate the length of each unit. For example, the YYMDHH format of your date would be 217100 (same date, different format).

A list of Airtable’s date unit shorthands can be found here: Supported format specifiers for DATETIME_FORMAT – Airtable Support

Great, thank you so much!