"Last Modified Date" export

Smart CRM & Sales Hub Tips, Tricks & Best Practices
1

Hi! I set up a workflow to export deals to Google Sheets based on a trigger. The workflow appears to be functioning properly, however, it exports the “LastModifiedDate” as a strange number that I seem unable to convert to a date, they look something like:

1697809811371
1700583807154
1700587620935
1700587623732
1700587625173
1700587706067
1700590017105

-- Is there a reason they are exported this way? Alternatively, does anybody know how to convert these to a proper date format? I’ve tried the function TO_DATE in Google Sheets to no avail

2

Hi @forestfranzose,

For dates in Google Sheets, HubSpot sends unix timestamps in milliseconds. To convert dates from milliseconds, you would create a separate column and add the the following formular:

=arrayformula(if(A2:A<>"",(A2:A/1000)/ 86400 + DATE(1970, 1, 1),""))

… replacing column A with the colum that the unix timestamp is in.

Alternatively, the EPOCHTODATE function should also work: EPOCHTODATE function - Google Docs Editors Help

Hope this helps!