1

I am working on creating a personal finance dashboard and therefore integrating different CSV files downloaded from different sources into one big table.

The problem I am facing is that the date format in my downloaded CSV files gets formatted differently within the same file/column, and thus when I try to convert it through pd.to_datetime in python it gets the date wrong on some values.

Here is a picture for reference: csv file

The left indented cells are formatted as General, while the right indented ones are formatted as Date.

When I just change the type of the whole column the ones already formatted as Date invert the Day/Month.

Any idea why this happens? It happens both to CSV files from my bank account and PayPal, so I assume there is some System setting I could change?

If not, is there any way to conditionally convert only the rows formatted as Date to General or the other way around? Thanks in advance.

Improve this question
4
  • It might be helpful if you can include how those dates (both of them) are actually in the CSV (when opened as text file in notepad or the like)
    – Yisroel Tech
    Commented Jan 3, 2023 at 13:13
  • 1
    Most likely, the CSV file contains dates formatted as Day/Month/Year and your Excel (or Windows) is configured to expect Month/Day/Year
    – Paul
    Commented Jan 3, 2023 at 17:29
  • Very common issue. If you Import the file into Excel, using either Power Query or the legacy text import wizard, you should be afforded the opportunity to tell Excel the date format of the CSV file, so it can interpret those date strings correctly.
    – Ron Rosenfeld
    Commented Jan 5, 2023 at 2:40
  • Try to use Power Query to import the .csv file and format the date when importing.
    – Lee
    Commented Jan 9, 2023 at 9:01

1 Answer 1

Reset to default
0

I had the same issue and I solved it doing this:

Select the your date column and go to the "Data" tab. Click "Text to Columns". Choose "Delimited" and click "Next". Uncheck all delimiters and click "Next". Select "Date" and choose the format that matches your date data (e.g., DMY for Day/Month/Year). Click "Finish".

Then you can format your cells as needed.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .