IEX Cloud's Excel plug-in, "IEX Cloud Stock Data," makes it easy to import financial datasets directly into spreadsheets. Certain data is returned in the form of dates and times – such as the "latestUpdate" property from the Quote endpoint, which provides a timestamp for the last time a stock price changed.
Timestamps returned from IEX Cloud are expressed in epochal time. Dates in Microsoft Excel, however, are expressed in sequential serial numbers, with "1" representing January 1st, 1900, "2" representing January 2nd, 1900, and so on.
To convert an IEX Cloud timestamp to a human-readable UTC date/time, apply the following formula (where the IEX Cloud timestamp is in cell B1):
=(B1 / 86400000) + 25569
Next, format the result to a readable date or time. Right-click the cell and select "Format Cells." Under "Date," select the desired format.
Note that exact steps to get to the "Number Format" option may vary by which version of Excel you are using.
To convert from UTC time to another time zone, apply the appropriate hour offset. For example, the following formula converts a timestamp from August 7th from UTC time to New York Time (GMT-4):
=(B1 / 86400000) + 25569 - 4/24