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

python excel openpyxl
MothraDactyl picture MothraDactyl · Jun 23, 2014 · Viewed 32.5k times · Source

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?

Answer

Scott Roberts picture Scott Roberts · Jun 25, 2015

I'm adding this as a new answer since I don't have enough reputation to add a comment to the above. 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)

Related questions

Pandas: Looking up the list of sheets in an excel file

The new version of Pandas uses the following interface to load Excel files: read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA']) but what if I don't know the sheets that are available? For example, …

Read More
getting sheet names from openpyxl

I have a moderately large xlsx file (around 14 MB) and OpenOffice hangs trying to open it. I was trying to use openpyxl to read the content, following this tutorial. The code snippet is as follows: from openpyxl import load_workbook …

Read More
how to write to a new cell in python using openpyxl

I wrote code which opens an excel file and iterates through each row and passes the value to another function. import openpyxl wb = load_workbook(filename='C:\Users\xxxxx') for ws in wb.worksheets: for row in ws.rows: print …

Read More