)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpot stores complete timestamp data but provides no built-in way to extract hour components for analysis, and its calculated properties can’t perform time-based extractions from existing timestamp fields.
You’ll learn how to access HubSpot’s complete timestamp data and use powerful extraction formulas to create the foundation for granular time analysis.
Access complete timestamp data with Coefficient
HubSpot’s custom fields can’t automatically populate with hour values from existing timestamp fields, leaving you unable to perform granular time analysis within the platform. But HubSpot does store full timestamp information that you can extract and manipulate in spreadsheets.
How to make it work
Step 1. Import full timestamp data from HubSpot.
Connect to HubSpot tickets and import the “Create Date” field, which contains complete timestamp information including hours, minutes, and seconds. This raw data is what you’ll use for all subsequent time extractions.
Step 2. Use hour extraction formulas.
In adjacent columns, use =HOUR(B2) where B2 contains your HubSpot timestamp to extract just the hour component in 0-23 format. This creates a new column showing only the hour when each ticket was created.
Step 3. Extract additional time components.
Add more time analysis columns using =WEEKDAY(B2) for day of week, =DAY(B2) for day of month, or =MINUTE(B2) for more granular analysis. Each formula targets a specific time component from the same timestamp.
Step 4. Enable automated formula application.
Turn on Formula Auto Fill Down so new tickets automatically get their hour components calculated when data refreshes. This eliminates manual work as your dataset grows.
Step 5. Apply time zone adjustments if needed.
For multi-timezone analysis, use formulas like =HOUR(B2+TIME(offset_hours,0,0)) to standardize timestamps across different regions. Replace “offset_hours” with the appropriate timezone difference.
Step 6. Create analysis-ready time groupings.
Build calculated columns for meaningful business periods using formulas like =IF(HOUR(B2)>=6,IF(HOUR(B2)<12,"Morning","Afternoon"),"Night") to group hours into business-relevant time ranges.
Build the foundation for time-based insights
This timestamp extraction creates the foundation for all subsequent hourly ticket analysis, transforming HubSpot’s raw date data into actionable time-based insights. Start extracting your timestamp data today.
