About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Question

Hubspot to Google Sheet date format issues

  • 18 April 2023
  • 6 replies
  • 214 views
pucimo

Hi,

 

I am trying to upload the date submitted in Hubspot form into Google Sheet.

But when testing the Zap, the Action section of Google Sheet shows “1699920000000” for the date while in Hubspot, “2023-11-11” (YYYY-MM-DD) has been input. I have tried the converter step before Google Sheet Action but I still get the same series of number. What could be the reason ?

 

Thanks

6 replies

SamB
Rank
Userlevel 7
Badge +11

Hi there @pucimo! 👋

Hmm, perhaps the formatting in the Google Sheet itself needs to be changed? 

Can you try selecting the column in the spreadsheet that should contain the date and change it’s formatting so that it matches the desired YYY-MM-DD format? Google has some instructions on how to change the date format here: Format numbers, dates and currencies 


Hope that helps. Please do let us know whether that does the trick!

Alban
Userlevel 1

After some researches, I figured out that this number is equal to every seconds passed from 01/01/1970. 

ken.a
Userlevel 7
Badge +6

Hi there @Alban,

If you’re also having trouble with Google Sheets formatting the date incorrectly. We have a great help article on how to solve this issue. You can find the article here: https://help.zapier.com/hc/en-us/articles/8495995099533-Common-Problems-with-Google-Sheets-on-Zapier#my-dates-are-coming-through-incorrectly-0-13

Hopefully, this helps! 😊

SamB
Rank
Userlevel 7
Badge +11

Thanks for letting us know, @Alban.


Ah, yes it looks to be in a unix time format. It looks like you’ll need to add ts to the start of the field that’s passing a unix timestamp to Google Sheets. For example:
52a9dce2f340cb756183e48fd6cb1454.png
Then that should help to convert it into a date/time value that Google Sheets can read. Then, if it’s not it the desired format you can change the formatting in the cells of that column to get it to display the date/time in the desired format.

Alternatively, you could use a Formatter (Date / Time > Format) action to convert it from unix time to the desired format. In the following example let’s use YYYY-MM-DD. Just be sure that on the From Format you select X. For example:
73d8b9946cfa947ca929b2203fee8839.png

Then on the Google Sheets action you can select the converted time from the Formatter action. That would look something like this:

9c383dd5a06a60b90a5421b7b55b96d4.png

Can you give either of those options a try and let us know if they work as hoped?

gschare
Badge

This workaround fixed the problem for me. But do any of you know what causes this? I have other zaps that use the same HubSpot field and are written to my google sheets as dates. This week was the first time I encountered a unix timestamp in a new zap I just created.

To make it even more mysterious, it’s sending the date in the correct format when I test the zap but as a unix timestamp in live runs after publishing. Below is test, live run, test, test, live run.

 

 

ken.a
Userlevel 7
Badge +6

Hi there @pucimo,

This issue is occurring because the HubSpot API endpoints accept UNIX formatted timestamps in milliseconds as opposed to more commonly-seen date formats. ​Fortunately, this can be resolved with a nifty tool we have called a Formatter Step. Formatter Steps can easily convert one date/time format into another and you can use one to transform the UNIX timestamp into something better for your needs. You can learn more about building this Formatter Step here: https://zapier.com/apps/formatter/help#date-time

If a you are sending data to a spreadsheet and doesn't want to use another Task on a Formatter step a workaround is to format the date within the spreadsheet "=((A1-21600000)/86400000)+25569 # Then select the column and use Format -> Number -> Date time"

Hope, this helps!

Reply


Terms & Conditions