Jan 18 2022 08:14 PM - edited Jan 18 2022 08:34 PM
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!
Jan 18 2022 08:45 PM
@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.
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.
Jan 18 2022 08:58 PM
You could use fomular
=DATE(VALUE(MID(A2,5,4)),VALUE(MID(A2,3,2)),VALUE(MID(A2,1,2)))
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
Jan 19 2022 12:48 AM