Converting Unix timestamp to date format in an Excel spreadsheet

Products

• CA IT Asset Manager
• ASSET PORTFOLIO MGMT- SERVER
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager
• CA Service Desk Manager
• ServiceDesk
• CA Service Catalog

Issue/Introduction

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

All Service Management components (Asset Portfolio Manager, Service Desk, Service Catalog) v11.3 and higher

Resolution

Dates 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.