Can't change datetime format

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:

df['Week']=pd.to_datetime(df['Week'], format='%m/%d/%Y').dt.date

What do you want the value of df['Week'] to be, a datetime.date or a str?

I need the datetime format to be 2/5/2022 but it comes out as
2022-05-02 when exported to Excel. […] format='%m/%d/%Y'

I first want to point out a couple of things here:

  • 2022-05-02 is the common ISO8601 format where the components go from
    large to small and also sort lexically, which is handy
  • ISO8601 uses year, month, day, so your example 2/5/2022 should be
    5/2/2022 to match what you seem to be saying
  • since format='%m/%d/%Y' expresses the common USA-specific month,
    day, year ordering (day/month/year is common elsewhere and a source of
    ambiguity)

So, to your approach:

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:

df['Week']=pd.to_datetime(df['Week'], format='%m/%d/%Y').dt.date

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.

So the flip side of this is: how did you get your data? Did it come from
read_excel?
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html#pandas.read_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.

Cheers,
Cameron Simpson cs@cskk.id.au