DEV Community

abbazs
abbazs

Posted on

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

Enter fullscreen mode Exit fullscreen mode




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')})

Enter fullscreen mode Exit fullscreen mode




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"])

Enter fullscreen mode Exit fullscreen mode




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")

Enter fullscreen mode Exit fullscreen mode




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")

Enter fullscreen mode Exit fullscreen mode




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

Enter fullscreen mode Exit fullscreen mode




Step 7: Save the workbook

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



ewb.save()

Enter fullscreen mode Exit fullscreen mode




How the result will look like?

Resultant excel document

Top comments (0)