Marketing Integrations

Damien
Contributor | Diamond Partner
Contributor | Diamond Partner

Date format when exporting to Google Sheet

SOLVE

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? 

1 Accepted solution
karstenkoehler
Solution
Hall of Famer | Partner
Hall of Famer | Partner

Date format when exporting to Google Sheet

SOLVE

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
Digital Marketer | HubSpot Freelancer | CRM Consultant

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

10 Replies 10
BJakharia
Participant

Date format when exporting to Google Sheet

SOLVE

Hi

This formula: =A1/1000/60/60/24 + DATE(1970,1,1) 

gives just the date whereas this timestamp has time in it too.

Is there any formula for:

1. Getting date + time both.

2. Getting just the time.

Also in which timezone we'll get this time and is it possible to convert this time into Pacific (with automatic day light saving) ?

0 Upvotes
SDay
Participant

Date format when exporting to Google Sheet

SOLVE

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
saps
Member

Date format when exporting to Google Sheet

SOLVE

Not sure if that's what you're looking for. But Airboxr (my product) syncs with Hubspot to pull data into Google Sheets and standardizes all dates into YYYY-MM-DD format. 

Airboxr is slightly different from connectors in that: instead of dumping your data from Hubspot into Google Sheets, you use it to create queries as output (e.g., a pivot table of no. of new contacts added by date). You can save those queries as Hops (sort of like Zaps in Zapier) and retrieve the data from any Google Sheet.

You can learn more and claim your free trial here.


0 Upvotes
karstenkoehler
Solution
Hall of Famer | Partner
Hall of Famer | Partner

Date format when exporting to Google Sheet

SOLVE

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
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


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

ryanjoconnor
Participant

Date format when exporting to Google Sheet

SOLVE

How is this not addressed as a bug? Why would any user assume this to be the correct outcome? 

SKealy
Contributor

Date format when exporting to Google Sheet

SOLVE

Especially when the 'solution' also doesn't work. PLEASE FIX HUBSPOT

0 Upvotes
SKealy
Contributor

Date format when exporting to Google Sheet

SOLVE

This gave me the right format, but the wrong date, it's about a year in the past. 😕

thanhdoan
Participant

Date format when exporting to Google Sheet

SOLVE

Fantastic and very useful. What formula would you use to produce the opposite? So standard date value (e.g., 13/06/2022) into a HubSpot unix timestamp?

 

Comment edited on 19/07/2022 - the answer is below.

 

(A2-DATE(1970,1,1))*86400000

0 Upvotes
PMontoya
Participant

Date format when exporting to Google Sheet

SOLVE

this formula is not working correctly for me. Is there something else I should b replacing?

0 Upvotes
spagnotta
Participant

Date format when exporting to Google Sheet

SOLVE

Hi @PMontoya, it didn't work for me either at first, until I changed the format to date (Format > Number > Date). Hope this helps! 

0 Upvotes