The timestamp 1620710374103 represents May 11, 2021, in Unix millisecond format – a common HubSpot date export format. While you could convert this manually using =(1620710374103/1000)/86400+DATE(1970,1,1), there’s a much simpler automated approach for handling these conversions.
Here’s how to automatically convert millisecond timestamps like 1620710374103 into readable dates without formula complexity.
Get automatic millisecond conversion using Coefficient
Coefficient’sHubSpottimestamp conversion capabilities handle the technical complexity of millisecond epoch time conversion automatically duringdata imports. This means timestamps like 1620710374103 appear as properly formatted dates (05/11/2021) without requiring formula intervention.
How to make it work
Step 1. Install Coefficient and connect to HubSpot.
Add Coefficient to Google Sheets from the Workspace Marketplace and open the sidebar. Connect your HubSpot account through “Connected Sources” to enable direct data access with automatic formatting.
Step 2. Select HubSpot objects with millisecond timestamps.
Choose your desired HubSpot objects (contacts, deals, companies) and select the date properties that contain millisecond timestamps. Coefficient identifies these fields and prepares them for automatic conversion.
Step 3. Import with precision timestamp handling.
Click “Import” to pull your data. Coefficient correctly handles millisecond-level timestamp precision from HubSpot, converting values like 1620710374103 into readable dates that Google Sheets recognizes for analysis.
Step 4. Apply consistent formatting across all records.
Coefficient applies uniform date formatting across all imported records, ensuring consistency whether you’re importing dozens or thousands of records with millisecond timestamps. Set up scheduled refreshes to maintain this formatting as new data flows in.
Skip the timestamp math and get clean date data
Start usingManual millisecond conversion becomes error-prone and time-intensive with large datasets containing multiple date properties. Coefficient handles the technical conversion automatically, delivering analysis-ready data with proper date functionality from the start.Coefficient for effortless timestamp conversion.