Forum Discussion

Buff_Daddy's avatar
Buff_Daddy
Copper Contributor
Jan 19, 2022

Change date format in CSV

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!

excel

3 Replies

  • Benny_1857's avatar
    Benny_1857
    Brass Contributor
    Jan 19, 2022

    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

    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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 19, 2022

    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.

Resources