Change date format in CSV

Occasional Visitor



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?


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.


You could use fomular 



Or Power Query


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


One more variant

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