I need the datetime format to be 2/5/2022 but it comes out as 2022-05-02 when exported to Excel. It also needs to be a date so when I go to filter it in Excel, it needs to be a date filter and not a text filter. Here is what I have:
This I think is the wrong way to think about this. I’m assuming the 'Week' column maybe represents the starting date of a week.
In your data frame you do not care about what Excel sees - you just
want to store a date. It’s like storing a float - in the data frame
is it just a number. You only care about Excel when you write out the
Excel file using to_excel: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html
which should do the correct thing with dates provided the 'Week'
column contains a date. This is because Pandas should know that that
column is a date, and therefore what to write for Excel.
That function has a parse_dates parameter which defaults to False.
Try calling it with parse_dates=True and see what your raw 'Week'
data look like - it should already be a date, and therefore correctly
written out again when you use to_excel.
It also accepts a list of column names, eg: parse_dates=['Week']
If this doesn’t match how you’re loading and saving data, please provide
more detail.