Format Date into normal format when pulling it to the spreadsheet

Data Hub Data Hub
1

Hey HubSpotters.

I have set up Google Sheets integration with HubSpot to pull Contact’s data into it.

All works fine, the only thing I’m trying to resolve is that instead of creating a normal Date and Time, HubSpot sends unix timestamps like this: “1681215438000” in milliseconds instead of usual date&time like this “2023-04-03 16:33”

I read an article and tried to use formatting data in Workflow, but not sure of the exact command to be used there, can you advise?

please see the screenshot Screenshot by Lightshot

2

Hi @tverdokhlib1,

Thanks for reaching out to the Community!

I wanted to share this related community thread where @karstenkoehler shares the formula to convert the UNIX format to datestamp. You can also use a UNIX Converter to view the data in standard date and time format. :slightly_smiling_face:

I hope this helps!

Best,

Kristen

3

Hi @kvlschaefer thank you, but I think you missed the link to the thread:)

4

Hi @tverdokhlib1,

Thanks for the heads up :slightly_smiling_face: It’s time for another coffee!
I’ve updated the post with a link to the related thread.

Please let us know if you have any questions!

Best,

Kristen

5

Thank you @kvlschaefer this is solution for Excel, I’m also looking for a solution through Operation Hub - formatting data via Workflow. The formals in Excel don’t work good when there is an automation, it’s better not to touch it manually when an automation is going, because then there is a mess and the data is pulled incorrectly

6

Hi @tverdokhlib1,

Thank you for the clarification regarding your goal.

Let’s invite our subject matter experts to this conversation.

Hi @ChrisoKlepke, @LMeert, @JBeatty - Do you have any insight for @tverdokhlib1?

Thank you!

Best,

Kristen

7

Hi @tverdokhlib1 - you can use Superjoin to pull data from HubSpot to Google Sheets automatically. They handle UNIX timestamp to Date+Timestamp conversion automatically. They also do internal value-->Label mapping for fields like Contact Owner etc.