While the formula =(A1/1000/86400)+DATE(1970,1,1) can convert HubSpot’s 13-digit create date timestamps, managing conversion formulas across multiple columns and large datasets quickly becomes unwieldy. There’s a more efficient approach that eliminates formula maintenance entirely.
Here’s how to get properly formatted HubSpot create dates without writing or maintaining conversion formulas.
Get formatted create dates automatically using Coefficient
Coefficient’sHubSpotintegration processes create date and other timestamp fields during import, eliminating manual formula conversion. This approach scales effortlessly whether you’re working with hundreds or thousands of contact records.
How to make it work
Step 1. Set up Coefficient in Google Sheets.
Install Coefficient from the Google Workspace Marketplace and open the sidebar. Connect your HubSpot account through “Connected Sources” to enable direct data access.
Step 2. Choose HubSpot contacts and create date property.
Select “Contacts” as your HubSpot object and locate the “Create Date” property in the available fields list. You can also select additional date properties like “Last Modified Date” or custom date fields.
Step 3. Import with automatic date formatting.
Click “Import” to pull your contact data. Coefficient automatically converts the create date timestamps into properly formatted dates that Google Sheets recognizes, ready for immediate use in pivot tables and analysis.
Step 4. Schedule regular updates to maintain formatting.
Configure scheduled imports to keep your contact data current. Each refresh maintains the proper date formatting without requiring formula updates or manual intervention, even as your contact database grows.
Skip the formula complexity for HubSpot date handling
Try CoefficientManual timestamp conversion formulas become error-prone and time-intensive with large contact databases. Coefficient handles the technical conversion automatically, delivering analysis-ready date data from the start.for hassle-free HubSpot date imports.