KB00062 - Converting date format in Excel for exported CSV files

Explanation

When a CSV file exported from the Manage dialog of Stickies is opened in Excel, the date column shows up as a ten digit number, rather than the date and time that sticky was stored.

The ten digit number is the CTime class way of specifying a time. It's the count of seconds passed since 01/01/1970. In turn, the CTime method was adapted from UNIX.

Method

This Excel formula will change the number into an Excel date and time:

=DATE(1970,1,1)+(A1)/86400

The red text is the reference to the cell containing the ten digit number.

You may then need to tell Excel that the cell containing the above formula should be formatted as a date and time. To do this, select the cell(s), right-click them and choose Format Cells... / Number / Custom, and paste:

dd/mm/yyyy hh:mm

into the Type: box.