Export of date picker field to Excel using Zapier

SOLVE
Highlighted
New Contributor

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

Reply
0 Upvotes
1 Accepted solution

Accepted Solutions
Highlighted
HubSpot Moderator

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'.

 

Reply
0 Upvotes
2 Replies 2
Highlighted
HubSpot Moderator

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'.

 

Reply
0 Upvotes
Highlighted
New Contributor

Hi Wendy, 

 

Your solution worked 👌🙌🍾. Thanks so much. 

 

Seb

Reply
0 Upvotes