how do I change date format on a list of dates (and retain that date format when saving to excel via openpyxl)?

Question

This code:

dateCol = []

for row in ws3.iter_rows(min_col=1, max_col=1, min_row=7, max_row = None):
    for cell in row:        
        dateCol.append((ws3.cell(row=cell.row, column=1).value))

dateCol

Produces this list:

[datetime.datetime(2019, 10, 7, 0, 0),
 datetime.datetime(2019, 10, 3, 0, 0),
 datetime.datetime(2019, 10, 3, 0, 0),
 datetime.datetime(2019, 10, 14, 0, 0),
 datetime.datetime(2019, 10, 15, 0, 0),
...]

In Excel the dates look like:

How do I convert these dates to mm/dd/yyyy? I have struggled with this as shown in (which I may delete soon as it less pointed):

Trying to format dates in column with openpyxl

@samliew You can please undelete the solution? This is not posted in multiple locations. I mistakenly posted this answer to the wrong question earlier but it was there for two minutes. I quickly deleted it. This is a useful answer to a tricky problem and concept. — Kierk, Oct 18 '19 at 20:26

score 2 · Answer 1 · answered Oct 18 '19 at 12:27

2

Try the following:

import datetime
dateCol = [datetime.datetime(2019, 10, 7, 0, 0),
     datetime.datetime(2019, 10, 3, 0, 0),
     datetime.datetime(2019, 10, 3, 0, 0),
     datetime.datetime(2019, 10, 14, 0, 0),
     datetime.datetime(2019, 10, 15, 0, 0)]


dateCol = [dt.strftime('%m/%d/%Y') for dt in dateCol]
print(dateCol)

Also you can change the excel date preview.

answered Oct 18 '19 at 12:27

Kostas Charitidis

2,991
1
12
23

1

@shaikmoeed yeap like a charm. Tested it with and without `strftime()` it fixes his issue. – Kostas Charitidis Oct 18 '19 at 12:33
1

`2019-10-07 0:00:00` that was the cell value before and now is `10/07/2019` – Kostas Charitidis Oct 18 '19 at 12:34

score 1 · Answer 2 · answered Oct 18 '19 at 12:25

1

Try this,

cell.number_format = 'MM/DD/YYYY'

answered Oct 18 '19 at 12:25

shaik moeed

5,300
1
18
54

Thx. I tried this: `import datetime as dt dateCol = [] for row in ws3.iter_rows(min_col=1, max_col=1, min_row=7, max_row = None): for cell in row: dateCol.append((ws3.cell(row=cell.row, column=1).value)) cell.number_format = 'MM/DD/YYYY' dateCol` but am returned: `[datetime.datetime(2019, 9, 10, 0, 0), datetime.datetime(2019, 10, 7, 0, 0), datetime.datetime(2019, 10, 3, 0, 0), datetime.datetime(2019, 10, 3, 0, 0),` – Kierk Oct 18 '19 at 13:54
I need to learn how to better format comments. They are hard to read. – Kierk Oct 18 '19 at 13:56
@Kierk In that case you’d need to change the number_format before appending it to dateCol otherwise you’re back to zero. So after your ‘for cell in row:’ cell.number_format.... – Kostas Charitidis Oct 18 '19 at 14:39
So I changed it to this but get same output (regardless I believe any save to excel will inherit an excel default date format anyways). `#Change date format in column A import datetime as dt dateCol = [] for row in ws3.iter_rows(min_col=1, max_col=1, min_row=7, max_row = None): for cell in row: cell.number_format = 'MM/DD/YYYY' dateCol.append((ws3.cell(row=cell.row, column=1).value)) dateCol'` – Kierk Oct 18 '19 at 14:49
results in the printed list are still: `[datetime.datetime(2019, 9, 10, 0, 0), datetime.datetime(2019, 10, 7, 0, 0), datetime.datetime(2019, 10, 3, 0, 0), datetime.datetime(2019, 10, 3, 0, 0),` – Kierk Oct 18 '19 at 14:51

Kierk · Answer 3 · 2019-10-18T14:03:44.627

Thanks guys.

So this code:

#Change date format in column A
import datetime as dt
dateCol = []

for row in ws3.iter_rows(min_col=1, max_col=1, min_row=7, max_row = None):
    for cell in row:
        dateCol.append((ws3.cell(row=cell.row, column=1).value))

dateCol = [dt.strftime('%m/%d/%Y') for dt in dateCol]
dateCol

outputs:

But when I save it to excel with: wb3.save('C:\folder\output.xlsx') I still see:

So perhaps using a list of the dates is not the correct approach? If so can python/openpyxl change the excel date preview of an existing worksheet as Kostas kindly suggests? I know how to do it manually in excel but selecting the cells and changing the date format but I need to fully automate all of this.

score 0 · Answer 4 · answered Oct 18 '19 at 14:17

0

It seems that I need to use python to set this date formatting (in excel) on the cells in the column:

Is this possible? With openyxl or some alternative?

answered Oct 18 '19 at 14:17

Kierk

476
6
23

Kierk · Accepted Answer · 2019-10-18T18:57:00.207

I figured it out..finally! I could not get a list of dates to save in Excel without Excel altering the date format. I then used openpyxl iter_rows to iterate through every cell in a single column and apply a date style to each cell.

The lesson I have learned here is that it is not enough to alter the data in a list and then save it to excel. Excel will alter the date format. You have to apply a Namedstyle to the cell to alter the date format not alter the cell value. Namedstyles are defined here:

https://openpyxl.readthedocs.io/en/stable/styles.html#cell-styles-and-named-styles

It all makes sense now.

from openpyxl import load_workbook
HISNonLive3 = "C:\\folder\\input.xlsx"
wb3=load_workbook(HISNonLive3)
ws3 = wb3['Non Live Integ Support Stage']

from openpyxl.styles import NamedStyle
date_style = NamedStyle(name='datetime', number_format='MM/DD/YYYY')
for rows in ws3.iter_rows(min_row=7, max_row=None, min_col=1, max_col=1):
    for cell in rows:
        cell.style = date_style
wb3.save('C:\\folder\\output.xlsx')

writes the following to an excel worksheet as:

I had done something similar with cell borders and colors using openpyxl stlyes. I then noticed this in the openpyxl style doc https://openpyxl.readthedocs.io/en/stable/styles.html:

number_format = 'General'

That was the clue I needed that if a cell has a date or number value it could be formatted by a style just like colors or borders.

how do I change date format on a list of dates (and retain that date format when saving to excel via openpyxl)?

5 Answers5