ExcelWriter ValueError: Excel does not support datetime with timezone when saving df to Excel
What format is your timestamps in?
I just had a similar problem.
I was trying to save a data frame to Excel. However I was getting:
I checked my date format which was in this format '2019-09-01T00:00:00.000Z'
This is a timestamp pandas._libs.tslibs.timestamps.Timestamp
from pandas.to_datetime
which includes a method date()
that converted the date into a format "%Y-%m-%d"
that was acceptable by excel
So my code was something like:
#Pseudo
df['date'] = old_dates
df['date'] = df['date'].apply(lambda a: pd.to_datetime(a).date())
# .date() removes timezone
...df.to_excel etc.
This should do the job, remove timezone from columns before exporting to excel (using tz_localize(None)).
# Check which columns have timezones datetime64[ns, UTC]
df.dtypes
# Remove timezone from columns
df['date'] = df['date'].dt.tz_localize(None)
# Export to excel
df.to_excel('filename.xlsx')