Date Re-Construction

“Date Re-Construction” is a utility for converting dates imported from database software into dates understandable by Excel spreadsheets (as dates).

This is in fact a very common problem encountered quite often by those who regularly have to used data from other data sources, such as database of other applications. When dates are imported into Excel, they’re not in a format which Excel can recognize as dates. Excel cannot perform calculation on such “dates” either.

For example, date data exported from an IBM AS/400 database could be in the format “YYYYMMDD” (i.e. 13-Jan-1999 is represented by 19990113). Such date format is not directly understandable by Excel and may not be useful in date calculations. In order for Excel to process the dates successfully, you may need to convert all those dates into the normal “MM/DD/YYYY" format, i.e. 01/13/1999.

“Date Re-Construction” is all you need to solve this everyday problem. It automatically re-construct all the dates quickly. Below shows a sample screen of the Date Re-Construction utility.

To use Date Re-Construction, simply select the cells which contain the dates you want to re-construct and run the function. You will be presented a form like the above one. You can either fill-in the blanks provided about the format of the source data; or you can select from a list of pre-defined data format which are most commonly used. (See below.)

Date Re-Construct In Action

A sample file for this section is including in the Excel Power Expander software, in the file: “Date Re-Construct.xls” in the “Examples” folder.

Here is a demonstration of how the “Date Re-Construction” function can be applied on real-life data.

Below shows a list of data originally stored in SUNAccounts (an accounting system), which uses a MS SQL database. The data was exported to a CSV (delimited text) file, and imported into Excel. The last column (Column E) contains the dates of transactions. Such dates couldn’t be recognized by Excel as dates. Excel should just consider them as text or numbers.

Now, select the cells which contain the dates (in column E), and run the “Date Re-Construction” function from the “Expander” menu.

A form will pop up, in which you need to fill-in the starting positions and lengths of the Year, Month and Day in the data. Let’s pick the data ““20020107”” as an example in our case:

Year start Position 1 Year length 4
Month start position 5 Month length 2
Day start position 7 Day length 2

Therefore, we fill-in the spaces provided accordingly, as shown in the figure below:

Click OK to process your data. The result is as shown in the figure below. (In column F)