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!

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

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! 

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:

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:

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

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

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.

 

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!