How can I correctly format dates using openpyxl?

Question

I am attempting to save dates in an existing Excel workbook using openpyxl but they are not formatted correctly in the cells when I open the workbook.

The dates are formatted in a list as such: mylist = ["09/01/2016","01/29/2016"]

The cells in which dates are being saved are formatted in the workbook as Date "*m/d/yyyy". When viewing the workbook, the dates are normally displayed in this format: 9/1/2016 1/29/2016

Using this code in Python:

SheetName.cell(row=2,column=1).value = mylist[0]

inserts the date formatted as 09/01/2016 and appears to be formatted as text (left justified).

If you click on the cell containing the date, then click on the formula bar, then press Enter, Excel formats and displays the date as 9/1/2016 (right justified).

I could spend time rewriting the code to strip leading zeros out of the month and day but this wouldn't address the formatting issue in Excel.

Is there a way to control the formatting of the cell when the date is added to a cell?

Answer 1

openpyxl supports datetime objects, which convieniently provide a way to convert from a string.

import datetime
for i in len(mylist):
    dttm = datetime.datetime.strptime(mylist[i], "%m/%d/%Y")
    SheetName.cell(row=i,column=1).value = dttm