How to format cell with datetime object of the form 'yyyy-mm-dd hh:mm:ss' in Excel using openpyxl

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?

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.

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 

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)