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-02is 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/2022should be
5/2/2022to 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