{"id":56,"date":"2025-07-13T10:15:00","date_gmt":"2025-07-13T07:15:00","guid":{"rendered":"https:\/\/fsaeed.blog\/?p=56"},"modified":"2026-10-31T00:18:52","modified_gmt":"2026-10-30T21:18:52","slug":"pandas-excel-timezone-error","status":"publish","type":"post","link":"https:\/\/fsaeed.blog\/index.php\/2025\/07\/13\/pandas-excel-timezone-error\/","title":{"rendered":"&#8220;Excel does not support datetimes with timezones&#8221;: Fixing It in pandas"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Hello again, tech enthusiasts! \ud83d\udc4b<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You have a nice Python script. It downloads orders from a web system, cleans them with pandas, and saves a report for your manager. Then one morning it stops with this message:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Nothing changed in your code. So what happened? Usually, the data source started sending dates with a time zone. Today I will explain the problem in simple words and show you a fix that works for all your columns at once.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What you need<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Python 3.9 or newer (<a href=\"https:\/\/www.python.org\">https:\/\/www.python.org<\/a>)<\/li>\n\n\n<li>pandas and openpyxl: <code>pip install pandas openpyxl<\/code><\/li>\n\n\n<li>A table with date columns (we will make a small example)<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Understand the problem<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In pandas, a date can be <strong>timezone-aware<\/strong> or <strong>timezone-naive<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A timezone-aware date knows its place in the world. For example: &#8220;10:00 in London&#8221;. It carries a label like <code>+01:00<\/code> or <code>UTC<\/code>.<\/li>\n\n\n<li>A timezone-naive date is just a clock time. For example: &#8220;10:00&#8221;. It does not say where.<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Excel only knows naive dates. An Excel cell has no place to store a time zone. So pandas stops and asks you to choose. This is a good thing! pandas does not want to quietly change your times.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s make the error happen on purpose:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import pandas as pd\n\ndf = pd.DataFrame({\n    \"order_id\": [101, 102, 103],\n    \"created_at\": pd.to_datetime(\n        [\"2025-07-01 08:30\", \"2025-07-01 14:05\", \"2025-07-02 23:50\"]\n    ).tz_localize(\"UTC\"),\n    \"amount\": [250.0, 99.5, 410.0],\n})\n\nprint(df.dtypes)\ndf.to_excel(\"orders.xlsx\", index=False)  # ValueError here<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Look at the printed types. The <code>created_at<\/code> column shows <code>datetime64[ns, UTC]<\/code>. The <code>UTC<\/code> part is the time zone.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Decide which clock you want<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Before you remove the time zone, decide which local time your readers expect. This is the important step.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If your team works in New York, convert to New York time first.<\/li>\n\n\n<li>If you just want the time as it is, remove the label only.<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><code>tz_convert<\/code> moves the time to another zone. <code>tz_localize(None)<\/code> removes the time zone label and keeps the clock time.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Option A: show the time in New York, then remove the label\ndf[\"created_at\"] = df[\"created_at\"].dt.tz_convert(\"America\/New_York\").dt.tz_localize(None)\n\n# Option B: keep the UTC clock time, just remove the label\n# df[\"created_at\"] = df[\"created_at\"].dt.tz_localize(None)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">With Option A, &#8220;2025-07-01 08:30 UTC&#8221; becomes &#8220;2025-07-01 04:30&#8221;. With Option B, it stays &#8220;08:30&#8221;. Both are fine. Just choose on purpose, and maybe write the zone in the column name, like <code>created_at_new_york<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Fix all date columns automatically<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Real reports often have many date columns: created, paid, shipped, updated. You do not want to fix them one by one. This function finds every timezone-aware column and fixes it.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import pandas as pd\n\ndef remove_timezones(df, target_tz=None):\n    \"\"\"Return a copy of df with no timezone-aware date columns.\n    If target_tz is given (e.g. \"America\/New_York\"), convert first.\"\"\"\n    df = df.copy()\n    for col in df.columns:\n        if isinstance(df[col].dtype, pd.DatetimeTZDtype):\n            if target_tz:\n                df[col] = df[col].dt.tz_convert(target_tz)\n            df[col] = df[col].dt.tz_localize(None)\n            print(f\"Fixed column: {col}\")\n\n    # The index can have a time zone too\n    if isinstance(df.index, pd.DatetimeIndex) and df.index.tz is not None:\n        if target_tz:\n            df.index = df.index.tz_convert(target_tz)\n        df.index = df.index.tz_localize(None)\n        print(\"Fixed the index\")\n    return df<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>pd.DatetimeTZDtype<\/code> is the type pandas uses for timezone-aware date columns. So the <code>isinstance<\/code> check finds exactly the columns that cause the error.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Write the Excel file<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Now save the report. <code>ExcelWriter<\/code> lets you write more than one sheet into the same file, and set a nice date format.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>clean = remove_timezones(df, target_tz=\"America\/New_York\")\n\nwith pd.ExcelWriter(\n    \"orders.xlsx\",\n    engine=\"openpyxl\",\n    datetime_format=\"yyyy-mm-dd hh:mm\",\n) as writer:\n    clean.to_excel(writer, sheet_name=\"Orders\", index=False)\n\nprint(\"Saved orders.xlsx\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Open the file. Your dates are real Excel dates, so you can sort and filter them. \ud83c\udf89<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common problems<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Error still happens, but no column is <code>DatetimeTZDtype<\/code><\/strong> \u2192 Some columns have the type <code>object<\/code> and hold mixed values. Convert them first: <code>df[col] = pd.to_datetime(df[col], utc=True)<\/code>, then run the function again.<\/li>\n\n\n<li><strong><code>TypeError: Cannot convert tz-naive timestamps<\/code><\/strong> \u2192 You called <code>tz_convert<\/code> on a column that has no time zone. Use <code>tz_convert<\/code> only on timezone-aware columns. The function above checks this for you.<\/li>\n\n\n<li><strong>Times are a few hours wrong<\/strong> \u2192 You probably used Option B when your readers expected local time. Use <code>target_tz<\/code> with your zone name.<\/li>\n\n\n<li><strong>Zone name not found<\/strong> \u2192 Use names like <code>Europe\/London<\/code> or <code>Asia\/Tokyo<\/code>, not short forms like &#8220;EST&#8221;.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Wrap-up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The error looks scary, but the fix is small. Excel cannot store a time zone, so you choose a local time, remove the label, and save. With the <code>remove_timezones<\/code> function, your report script will not break again when the data source changes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next idea: add the time zone name to the sheet title or a note cell, so readers always know which clock the report uses.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn why pandas refuses to save some dates to Excel and how to fix every date column with a few lines of code.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[7,44,5,13,12],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-automation","tag-dates","tag-excel","tag-pandas","tag-python"],"_links":{"self":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":1,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/56\/revisions\/77"}],"wp:attachment":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}