Date format when exporting to Google Sheet

SOLVE
Damien
Contributor | Diamond Partner

Hi all, we are using the Google Sheets integration to export a list of contacts from HubSpot to Google Sheets via a workflow. 

 

The problem is that the "Create Date" property comes through as a long number, rather than an actual date. Here are some examples of the date property as it comes out of HubSpot: 

 

1620710374103
1611800442730
1616609290112

 

I am not sure how this translates into an actual date and time, could it be some sort of counter? Either way, is there a way to convert this information into a date within Google Sheets? 

0 Upvotes
1 Accepted solution

Accepted Solutions
karstenkoehler
Solution
Most Valuable Member

Hi @Damien,

 

For dates in Google Sheets, HubSpot sends unix timestamps in milliseconds. To convert dates from milliseconds, you would create a separate column and add the the following formular: =A1/1000/60/60/24 + DATE(1970,1,1) – replacing A1 with the cell that the unix timestamp is in.

 

Hope this helps!

Karsten Köhler
Freelance Digital Marketing Advisor

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

View solution in original post

2 Replies 2
karstenkoehler
Solution
Most Valuable Member

Hi @Damien,

 

For dates in Google Sheets, HubSpot sends unix timestamps in milliseconds. To convert dates from milliseconds, you would create a separate column and add the the following formular: =A1/1000/60/60/24 + DATE(1970,1,1) – replacing A1 with the cell that the unix timestamp is in.

 

Hope this helps!

Karsten Köhler
Freelance Digital Marketing Advisor

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

View solution in original post

SDay
Participant

I'm exporting dates to a word document through Zapier and then into Formstack. Is there a way to set an export format for dates that isn't in milliseconds, since I can't run a formula in my word doc?

0 Upvotes