Overview

If you have a column of dates that has a numeric data type, you can change the data type to date/time. For example, suppose you have a numeric column of dates in YYYYMMDD format. In this format, the value 20150101 represents the first day of the year 2015. You want to convert the numeric column to a date/time column in MM/DD/YYYY format. There are two ways to do this: use the format column command or use the Calculator.

In the following examples, the numeric column of dates in YYYYMMDD format is in column C1 and C2 is empty.

Use the format column command to convert the data type

With the format column command, you change the data type of the column first to text and then to date/time. The text to date/time conversion lets you specify the date/time format.

  1. Click in the column and choose Editor > Format Column.
  2. In Choose type, select Text. Click OK.
  3. Click in the column and choose Editor > Format Column.
  4. In Choose type, select Date.
  5. In Custom date format, enter yyyymmdd. Click OK in each dialog box.
  6. Click in the column and choose Editor > Format Column.
  7. In Date display formats, select 01/23/2050. Click OK.

Use the calculator to convert the data type

Using the calculator, you can convert the data in one step. The result is stored in C2.

  1. Choose Calc > Calculator.
  2. In Store result in variable, enter C2.
  3. Copy and paste the following into Expression: DATE(CONCATENATE(MID(TEXT(C1),5,2),"/",RIGHT(TEXT(C1),2),"/",LEFT(TEXT(C1),4)))
  4. Click OK.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy