Change date format in CSV

Occasional Visitor

G'day,

 

I've downloaded a CSV report from out bank account and the date column is in the format dmmyyyy, that is, it's just numbers.  Is there any easy way to change it to dd/mm/yyyy without having to manually change each cells?

Thanks!

3 Replies

@Buff_Daddy If the dates come like in the picture below (column A) with either one or two digits for the day, you could use a formula to create a real date.

Screenshot 2022-01-19 at 05.34.39.png

If, on the other hand, the days are always two digits (e.g. 05122021, thus with a leading zero for days 1 to 9), then Text-to-column on the Data ribbon is easier. In step 3 of 3, select the column with the dates and set the data type to Date "DMY" and press Finish. The same would be valid when using the (legacy) Text Import wizard to import the CSV file. Alternatively, connect to the CSV file using Power Query.

@Buff_Daddy 

You could use fomular 

=DATE(VALUE(MID(A2,5,4)),VALUE(MID(A2,3,2)),VALUE(MID(A2,1,2)))

 

Or Power Query

Benny_1857_0-1642568109899.pngBenny_1857_1-1642568137812.png

By the way, you could set up local time format in PQ but the out put is text. If the new date need to use for calculation it is better you set up it in Format Cells

@Buff_Daddy 

One more variant

=DATE( MOD(A1, 10000), MOD(INT(A1/10000),100), INT(A1/1000000))