So, given:

dttm = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
ws['A1'] = dttm

The result in excel is that the correct date-time is written to the cell (you can see it where you'd input formulas). BUT, the cell display format is only MM/DD/YYYY.

I need the cell to display like "6/23/2014 13:56" instead of just "6/23/2014".

How can I explicitly format the cell to accomplish this?

Thanks!

Edit

@alecxe This solution works and is exactly what I asked for. I would like to be able to save styles like the solution by @Woodham. Unfortunately it raises a typeError (see comment). Any suggestions?

0

6 Answers

The simplest way to format a cell is using .number_format = "format" as in:

value = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
cell = ws['A1']
cell.value = value
cell.number_format = 'YYYY MMM DD'

This is tested in openpyxl (2.2.2)

For openpyxl 2.4.5 you'll no longer have access to NumberFormat and Style and will have to use NamedStyle. Here's some sample usage:

from openpyxl.styles import NamedStyle
date_style = NamedStyle(name='datetime', number_format='DD/MM/YYYY HH:MM:MM')
ws['A1'].style = date_style

Alternatively with the new NamedStyle class, you can set the style by the string name once NamedStyle has been instantiated:

from openpyxl.styles import NamedStyle
NamedStyle(name='custom_datetime', number_format='DD/MM/YYYY HH:MM:MM')
ws['A1'].style = 'custom_datetime'

Documentation here:

I believe you will need to set a openpyxl.styles.Style on the cell(s) that you want to format.

Looking at the documentation here, something like this should work:

dttm = datetime.datetime.strptime("2014-06-23 13:56:30", "%Y-%m-%d %H:%M:%S")
s = Style(number_format=NumberFormat('dd-mm-yyyy h:mm:ss'))
ws['A1'] = dttm
ws['A1'].styles = s

Update:Style class is no longer used, for the solution refer to this answer.

2

For openpyxl 2.3.4 the NumberFormat cannot be imported, but this code works to set the style:

from openpyxl.styles import Style
…
date_style = Style(number_format="DD/MM/YYYY HH:MM:MM")
ws['A1'].style = date_style

4

I found that this worked. Although number_format is used it seems to recognise the date format specified when put into the excel wb.

import datetime
date = datetime.date(2020, 2, 24) # python datetime format is yyyy mm dd
ws.cell(row=[row_ref], column=[col_ref], value=date)
ws.cell(row=[row_ref], column=[col_ref]).number_format = 'dd/mm/yy'

from openpyxl import load_workbook
from openpyxl.styles import NamedStyle
xlsx_file = args.xlsx_file.name
# openning:
wb = load_workbook(filename = xlsx_file)
# create date style:
date_style = NamedStyle(name='date_style', number_format='DD.MM.YYYY HH:MM:MM')
# apply the style to the column H of the default sheet:
ws = wb.active
for row in ws[2:ws.max_row]: # skip the header cell = row[7] # column H cell.style = date_style
# saving:
wb.save(xlsx_file)

Edit: the above works for me, but somehow does not work on my coleagues machine. Converting the cell to string fixed that:

import datetime
from openpyxl import load_workbook
from openpyxl.styles import Alignment
xlsx_file = 'file.xlsx'
date_format = '%Y-%b-%d'
# openning:
wb = load_workbook(filename = xlsx_file)
# we also center align that column:
alignment = Alignment(horizontal='center')
# apply python date format to column H of the default sheet, and convert the column to Excel text:
ws = wb.active
for row in ws[2:ws.max_row]: # skip the header cell = row[7] # column H if isinstance(cell.value, datetime.datetime): cell.value = cell.value.strftime(date_format) cell.alignment = alignment
# saving:
wb.save(xlsx_file)

The same wrapped in a script:

#!/usr/bin/env python3
import argparse
import datetime
from openpyxl import load_workbook
from openpyxl.styles import Alignment
# ==============
## parsing args:
desc="""
Applies python date format to a given column of the xlsx file (default sheet) and converts the column to a Excel text format.
Dependencies:
pip3 install --user --upgrade openpyxl
"""
parser = argparse.ArgumentParser(description=desc, formatter_class=argparse.RawDescriptionHelpFormatter)
parser.add_argument('--version', action='version', version='%(prog)s 0.01')
parser.add_argument('-f', '--file', help = "xlsx file", dest = 'xlsx_file', type = argparse.FileType('r'), )
parser.add_argument('-c', '--column', help = "column (starting from A) (default to %(default)s)", dest = 'column', type = str, default = "A", )
parser.add_argument('-d', '--date-format', help = "date format to use, e.g. %%d.%%m.%%Y (default to %(default)s)", dest = 'date_format', type = str, default = '%Y-%b-%d', )
args = parser.parse_args()
# =========
## program:
xlsx_file = args.xlsx_file.name
column_number = sum( [ ord(char) - 97 + i*26 for i,char in enumerate( list( args.column.lower() ) ) ]
)
# openning:
wb = load_workbook(filename = xlsx_file)
# we also center align that column:
alignment = Alignment(horizontal='center')
# apply python date format to a given column of the default sheet, and convert the column to Excel text:
ws = wb.active
for row in ws[2:ws.max_row]: # skip the header cell = row[column_number] if isinstance(cell.value, datetime.datetime): cell.value = cell.value.strftime(args.date_format) cell.alignment = alignment
# saving:
wb.save(xlsx_file)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.