How to Get Datastore Dates to display in Excel

Jarrod Augustson
Platform Admins, Data Managers, Form Developers
How to use and export from Datastore; General Excel use

Excel does not automatically recognize the ISO 8601 format, the format dates are stored in and exported in for Datastores, as a "valid" Date/Time value. Therefore if you are ever exporting from a Datastore and need to convert the stored Date/Time value (2019-06-11T19:04:46.471Z) into a Date/Time that Excel can use (6/11/19 7:04 PM):

Use the following Excel Function:

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

***Change A1 to be your specific cell.

Then format the field/column to be whatever Date or Date/Time you want/need.

Note: These times are all in GMT. Additional conversions are necessary to take into account timezones.

Click here for an example Excel spreadsheet.