Export of date picker field to Excel using Zapier

[ExcellMarketing]

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:

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

Thanks

Seb

[SDay]

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?

[WendyGoh · Accepted solution]

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

[ExcellMarketing]

Hi Wendy, 

Your solution worked 👌🙌🍾. Thanks so much. 

Seb