I would like to retrieve a date from the MDB. It is in Unix format. How can I convert a date stored in our MDB database as an integer Unix timestamp into a human-readable date format in an Excel spreadsheet?

Document ID : KB000024040
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue:

I would like to retrieve a date from the MDB. It is in Unix format. How can I convert a date stored in our MDB database as an integer Unix timestamp into a human-readable date format in an Excel spreadsheet?

 

Environment: Windows

 

Resolution:

Dates in UAPM are stored in the MDB database in integer type fields using the standard Unix timestamp format (number of seconds since 12:00:01am, January 1, 1970). If you perform an advanced search which includes a date field and save the results to an Excel spreadsheet file (.csv), you see only the integer number instead of a human-readable date in the spreadsheet.

To convert a Unix timestamp to date format in Excel, you can use a formula.

If cell C1 contains the value you are trying to convert, enter the following formula in another cell to get the date result:
=(C1/86400)+DATE(1970,1,1)

This works because Excel stores a date as the number of days since December 31, 1899. This function converts the Unix timestamp (in C1) from seconds to days, then adds the date value for the Unix base date (January 1, 1970) to the result.