APIs & Integrations

ExcellMarketing
Member

Export of date picker field to Excel using Zapier

SOLVE

Hi Guys, 

 

I'm using Zapier to auto polulate an Excel doc with a date picker field from HubSpot but the result i'm getting is a long number e.g. "1579478400000".  

 

The property in set correctly in Hubspot:

 

HubSpot.PNG

 

Is there a formula I can apply in Excel to conver the number into the actual date?

 

Thanks

 

Seb

0 Upvotes
1 Accepted solution
WendyGoh
Solution
HubSpot Employee
HubSpot Employee

Export of date picker field to Excel using Zapier

SOLVE

Hey @ExcellMarketing,

 

Researching into this, I found this useful stackoverflow discussion: Convert milliseconds to date (in Excel) - Stack Overflow and trying it out on excel, I'm able to see that it works!

 

On cell A5, I paste value - '1579478400000' and on cell A6, I set the following formula:

 

=(A5 / 86400000) + DATE(1970,1,1)

Next, I format the cell with custom format - 'mmm-dd-yyyy hh:mm:ss' and I'm seeing the converted datetime on cell A6 as follows: 'Jan-20-2020 00:00:00'.

 

View solution in original post

0 Upvotes
3 Replies 3
SDay
Participant

Export of date picker field to Excel using Zapier

SOLVE

I am using Zapier to pull a date field from a deal into Formstack and create a word document. My dates are appearing as Seb's above, but since I'm not in Excel or Google Sheets I can't use a formula. Is there a way to set the export format to date from Hubspot?

0 Upvotes
WendyGoh
Solution
HubSpot Employee
HubSpot Employee

Export of date picker field to Excel using Zapier

SOLVE

Hey @ExcellMarketing,

 

Researching into this, I found this useful stackoverflow discussion: Convert milliseconds to date (in Excel) - Stack Overflow and trying it out on excel, I'm able to see that it works!

 

On cell A5, I paste value - '1579478400000' and on cell A6, I set the following formula:

 

=(A5 / 86400000) + DATE(1970,1,1)

Next, I format the cell with custom format - 'mmm-dd-yyyy hh:mm:ss' and I'm seeing the converted datetime on cell A6 as follows: 'Jan-20-2020 00:00:00'.

 

0 Upvotes
ExcellMarketing
Member

Export of date picker field to Excel using Zapier

SOLVE

Hi Wendy, 

 

Your solution worked 👌🙌🍾. Thanks so much. 

 

Seb

0 Upvotes