CODEWITHSUNDEEP
pythonpandasdatetimeopenpyxl
Khalid Amin
Khalid Amin

Reputation: 902

openpyxl how to set cell format as Date instead of Custom

I am using openpyxl and pandas to generate an Excel file, and need to have dates formatted as Date in Excel. The dates in exported file are formatted correctly in dd/mm/yyyy format but when I right-click on a cell and go to 'Format Cells' it shows Custom, is there a way to change to Date? Here is my code where I specify date format.

 writer = pd.ExcelWriter(dstfile, engine='openpyxl', date_format='dd/mm/yyyy')

I have also tried to set cell.number_format = 'dd/mm/yyyy' but still getting Custom format in Excel.

Upvotes: 1

Views: 6175

Answers (2)

Farley
Farley

Reputation: 21

The answer can be found in the comments of Converting Data to Date Type When Writing with Openpyxl.

ensure you are writing a datetime.datetime object to the cell, then:

.number_format = 'mm/dd/yyyy;@'  # notice the ';@'

e.g.,

import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = datetime.datetime(2021, 12, 25)
ws['A1'].number_format = 'yyyy-mm-dd;@'
wb.save(r'c:\data\test.xlsx')

n.b. these dates are still a bit 'funny' as they are not auto-magically grouped into months and years in pivot tables (if you like that sort of thing). In the pivot table, you can manually click on them and set the grouping though: https://support.microsoft.com/en-us/office/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725

Upvotes: 2

Dror Av.
Dror Av.

Reputation: 1214

You might have to convert them to datetime objects in python if they are saved as strings in the data frame. One approach is to iterate over the cells and doing it after using ExcelWriter:

cell = datetime.strptime('30/12/1999', '%d/%m/%Y')
cell.number_format = 'dd/mm/yyyy'

A better approach is to convert that column in the data frame prior to that. You can use to_datetime function in Pandas for that.

See this answer for converting the whole column in the dataframe.

Upvotes: 0

Related Questions