topic Re: Date format when exporting to Google Sheet in Marketing Integrations

Date format when exporting to Google Sheet

Damien — Fri, 30 Jul 2021 00:27:43 GMT

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?

Re: Date format when exporting to Google Sheet

karstenkoehler — Thu, 23 Feb 2023 14:05:19 GMT

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:

=arrayformula(if(A2:A<>"",(A2:A/1000)/ 86400 + DATE(1970, 1, 1),""))

... replacing column A with the colum that the unix timestamp is in.

Hope this helps!

Re: Date format when exporting to Google Sheet

SDay — Fri, 10 Sep 2021 17:49:16 GMT

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?

Re: Date format when exporting to Google Sheet

saps — Wed, 06 Oct 2021 08:03:36 GMT

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.

Re: Date format when exporting to Google Sheet

PMontoya — Fri, 04 Feb 2022 21:41:13 GMT

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

Re: Date format when exporting to Google Sheet

spagnotta — Thu, 17 Feb 2022 20:13:54 GMT

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

Re: Date format when exporting to Google Sheet

BJakharia — Fri, 08 Apr 2022 13:39:23 GMT

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) ?

Re: Date format when exporting to Google Sheet

thanhdoan — Tue, 19 Jul 2022 09:59:17 GMT

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

Re: Date format when exporting to Google Sheet

SKealy — Wed, 27 Jul 2022 23:46:32 GMT

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

Re: Date format when exporting to Google Sheet

ryanjoconnor — Tue, 16 Aug 2022 14:07:07 GMT

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

Re: Date format when exporting to Google Sheet

SKealy — Tue, 16 Aug 2022 23:44:01 GMT

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

Re: Date format when exporting to Google Sheet

ChrisPot — Wed, 19 Apr 2023 10:47:41 GMT

Hello,

I have found a formula which is working on my Gsheet "=epochtodate(A1;2)"

A1 needs to be replaced.

Re: Date format when exporting to Google Sheet

Leopoldo — Mon, 24 Apr 2023 21:07:28 GMT

Works perfect! thanks!!

Re: Date format when exporting to Google Sheet

EDelGaudio — Mon, 19 Jun 2023 08:00:08 GMT

Hello,

with the date before 1970, how we could solve?

Re: Date format when exporting to Google Sheet

fabiogomesds — Fri, 07 Jul 2023 17:12:21 GMT

best way! thanks!

Re: Date format when exporting to Google Sheet

JMolos — Mon, 21 Aug 2023 13:33:17 GMT

Hello Karsten!

How about the Created by user ID? I'm exporting that property in a Google sheet, but I came out as numbers.
Can you help me with this?
Thank you!

Re: Date format when exporting to Google Sheet

czuriguel — Wed, 27 Sep 2023 13:46:35 GMT

Is more easy, only you need this formula:

= EPOCHTODATE ( B2 ; 2 )

(take into account the spaces, eliminate them)

🙂

Re: Date format when exporting to Google Sheet

EDelGaudio — Sat, 20 Apr 2024 05:50:29 GMT

How is the procedure to format the date BEFORE in workflow and not in Google sheet with formula?

Re: Date format when exporting to Google Sheet

CEsposito — Thu, 13 Jun 2024 23:24:26 GMT

What does A1 need to be replaced by?

Re: Date format when exporting to Google Sheet

BOlson8 — Fri, 14 Jun 2024 19:47:37 GMT

I think you answered the question and I tried your formula and I must have done something wrong because i got #ref1