Help

 | CliFlo Home | close window
Search on keyword:  

Convert a Cliflo Oracle Date YYYYMMDD:HH24MI to an Excel date

Sometimes when downloading a file having choosen the Excel format dates in the data are still in the default CLIDB "YYYYMMDD:HH24MI" format eg "20120623:1330" (HH24 is the hour in 24 hour time).

The best choice is to use the date format "Excel (d/m/yyyy hh:mi)" - Excel will recognise and convert internally into a date.

Otherwise use the following formula to convert from "YYYYMMDD:HH24MI" to "d/m/yyyy hh:mi":

Short Version

In the (blank) cell to the right of the date to be converted insert the formula: "=DATE(LEFT(B10,4),MID(B10,5,2),MID(B10,7,2))+IFERROR(TIME(MID(B10,10,2),MID(B10,12,2),0),0)" (without the beginning and end quotes). Note the "IFERROR" section prevents an Excel "#VALUE!" error message if there is no time component (ie no ":HHMI").

Long Version

  1. We assume the dates requiring conversion to be in column "B" an we want to converted dates to be in column "C". For this example the source range of cells will be "B10:B58" and the destination range of cells to be "C10:C58".
  2. Select "C9:C58" (to include column header) and "Insert" / "Shift cells right" to create a new blank column.
  3. In cell "C10" insert the formula "=DATE(LEFT(B10,4),MID(B10,5,2),MID(B10,7,2))+IFERROR(TIME(MID(B10,10,2),MID(B10,12,2),0),0)" (without the beginning and end quotes).
  4. Select cells "C10:C58" and "fill down" (probably Control D).
  5. Ensure the column is sufficiently wide to handle the date otherwise the cell(s) are displayed as "####".
  6. These cells should now be recognised by Excel as dates and can be formatted in various Excel date formats using "Format Cells" / "Number".
  7. If desired the original dates can be replaced by "Copy" and "Paste Special" using "Values and number formats" then delete the cells "C10:C58".

See also: