I’m struggling with converting a timestamp from Hubspot into a proper date in Google Sheets. When I get data from Hubspot through Zapier, the dates come as long numbers like 1574842022157.
I tried using the standard Unix timestamp conversion method =timestamp/86400+DATE(1970,1,1) but it’s giving me weird results. When I test with a regular Unix timestamp like 1500598288, the formula works fine and shows 2017-07-21.
However, when I use the Hubspot number 1574842022157 with the same approach, I get a date way in the future like 51804-10-02. This doesn’t make sense.
The strange part is that online timestamp converters show the correct date for 1574842022157 as November 27, 2019. So there must be something different about how Hubspot formats their timestamps.
I noticed that Hubspot timestamps are much longer numbers than regular Unix timestamps. Maybe they include milliseconds or use a different starting point?
Has anyone figured out the right formula to convert these Hubspot date numbers in Google Sheets? I need to make this work for my automated workflow.
Same exact problem here with Hubspot data! Hubspot uses epoch time with milliseconds, but standard Unix timestamps are in seconds. Your formula works for Unix seconds but needs tweaking for milliseconds. You’ve got to convert milliseconds to seconds first by dividing by 1000. So it becomes =(timestamp/1000)/86400+DATE(1970,1,1). I just use =(timestamp/86400000)+DATE(1970,1,1) - does the same thing in one step by dividing by 86400000 instead of 86400. Been using this for all my Hubspot pulls for a year now, works every time.
yep, hubspot timestamps are in ms, which makes em longer. first divide by 1000, then use your formula. so change =timestamp/86400+DATE(1970,1,1) to =(timestamp/1000)/86400+DATE(1970,1,1). that helped me out too last month!
Your Hubspot timestamp 1574842022157 is in milliseconds, not seconds like regular Unix timestamps. That’s why you’re getting that crazy future date when you divide by 86400. Here’s the fix: convert milliseconds to seconds first by dividing by 1000, then use your formula. Try =timestamp/1000/86400+DATE(1970,1,1) or the cleaner version =(timestamp/1000/86400)+25569. The 25569 is just Google Sheets’ way of handling the Unix epoch. I’ve done this with several Hubspot integrations and it works every time. Pro tip: when you see those super long timestamp numbers from APIs, they’re almost always milliseconds.