Forum Discussion

BRJack's avatar
BRJack
Copper Contributor
Jan 19, 2025

How can I clean this date data in Excel?

Hello, 

 

I have an issue with this date column in a dataset I'm working with. I don't know why but when I open the file in excel, each date is either just a date as a General format or it's in a Date format but it's the wrong date!

The date in row 3 for example should read 2020-12-09 but instead says its 2012-09-20.

I've tried converting to a date format in which nothing appears to happen, and when I convert everything to General all of the incorrect dates become the serial numbered dates.

I did find a pretty easy solution, which was just use Pandas and Python to change the values in this column to a datatype instead of string. I'd like to know how to fix this in Excel though as I'm sure there's an easy way. Any help is appreciated, here's what the fixed version looks like.

dates.csv

dates_fixed.csv

  • The dates are in mm/dd/yy format while your format is yyyy-mm-dd. If Excel can interpret an mm/dd/yy value as an yyyy-mm-dd value, it will convert it to a date, otherwise it will leave it as text.

    You can use Power Query (Data tab of the ribbon > From Text/CSV in the Get & Transform Data group) to convert all the values correctly.

  • The dates are in mm/dd/yy format while your format is yyyy-mm-dd. If Excel can interpret an mm/dd/yy value as an yyyy-mm-dd value, it will convert it to a date, otherwise it will leave it as text.

    You can use Power Query (Data tab of the ribbon > From Text/CSV in the Get & Transform Data group) to convert all the values correctly.

    • BRJack's avatar
      BRJack
      Copper Contributor

      Awesome that works! Also figured out another way using Text to Columns in Data > Data Tools to change the format from yyyy-mm-dd to what I needed. Thank you!

Resources