How to Change Date Format in Excel using Openpyxl?

How to Change Date Format in Excel using Openpyxl

When working with date data in Excel using the openpyxl library, you may need to change the date format to a specific format before writing it to an Excel file. In this tutorial, we will guide you through the process of changing the date format and writing it to an Excel file using openpyxl.

Step 1: Import the required libraries

To begin, we need to import the necessary libraries: pandas, openpyxl, and openpyxl.styles.

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import NamedStyle

Step 2: Create a DataFrame with date column

Next, we'll create a DataFrame with a column of dates. In this example, we'll generate a range of dates from '2018-1-1' to '2019-1-1'.

df = pd.DataFrame({'DATE': pd.date_range(start='2018-1-1', end='2019-1-1')})

Step 3: Assign the date column to multiple columns in the DataFrame

To demonstrate different date formats, we'll assign the date column to multiple new columns in the DataFrame.

df = df.assign(DATE2=df["DATE"])
df = df.assign(DATE3=df["DATE"])

Step 4: Create an Excel workbook and named styles

We'll create an Excel workbook using pd.ExcelWriter and specify the engine as "openpyxl". Additionally, we'll define three named styles with different date formats.

ewb = pd.ExcelWriter('test.xlsx', engine="openpyxl")
nsmmyy = NamedStyle(name="cd1", number_format="MM-YY")
nsmmmyy = NamedStyle(name="cd2", number_format="MMM-YY")
nsbyy = NamedStyle(name="cd3", number_format="MMMM-YY")

Step 5: Write the DataFrame to the workbook

We'll write the DataFrame to the Excel workbook using the to_excel method of the Excel writer.

df.to_excel(excel_writer=ewb, sheet_name="SHT1")

Step 6: Apply named styles to the date columns

To change the date format in Excel, we'll iterate over each cell in the date columns and apply the appropriate named style.

ws = ewb.book["SHT1"]
for i in range(1, len(df) + 2):
    ws.cell(row=i, column=2).style = nsmmyy
    ws.cell(row=i, column=3).style = nsmmmyy
    ws.cell(row=i, column=4).style = nsbyy

Step 7: Save the workbook

Finally, we'll save the workbook using the save method of the Excel writer.

ewb.save()

How the result will look like?