1

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.

Dror Av.
  • 1,184
  • 5
  • 14
Khalid Amin
  • 872
  • 3
  • 12
  • 26

2 Answers2

2

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

Farley
  • 21
  • 3
0

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.

Dror Av.
  • 1,184
  • 5
  • 14
  • I have tried to set cell.number_format but still it appears as Custom in Cell Formatting – Khalid Amin May 27 '20 at 00:56
  • 1
    @KhalidAmin you need to save it as a `datetime` object, not a string. How is it defined in your dataframe? – Dror Av. May 27 '20 at 09:20
  • I am converting it to a date using srptime before applying the number_format but still it doesn't work – Khalid Amin Jun 03 '20 at 01:51
  • @KhalidAmin thats weird, can you update the question and maybe add more code so we can try and reproduce the error? When I do it here it seems to be working fine. – Dror Av. Jun 03 '20 at 10:26