Importing Dates

2.45K viewsGeneral Discussion


I have a little problem with a CSV file that I got. There are two columns with dates, but the format is different from normal:

It has: Year-month-day 20040825

Quantrix reads this as a straight number. Is there a way to convert this back into the expected format 8/25/2004?



Good question. Often .csv files that contain dates import the julian date – or puncuation that allows the system to detect that a date is coming in. The way your .csv is formatted makes it look just like a regular number – as you said.

One option is to convert the number into a date by using this formula:

A2 = date(value(left(A1,4)),value(mid(A1,5,2)),value(right(A1,2)))

Assuming the date ‘values’ are in A1, this formula chops up the pieces of the number into a year, month and day argument and uses the Date function to convert the value to a date.

Maybe someone else has a different approach…?