1

Question about formatting data when writing to an Excel doc as I am a bit newer to using Openpyxl.

I have an excel sheet that I am writing data to where one of the columns is a column that holds the current date in 'mm/dd/yyyy' format. Currently when writing to the Excel doc, my code reformats the date to 'yyyy-mm-dd' format, and the excel doc does not recognize the data as 'Date' type, but as 'General' data type.

Here is my Python code to write the date to the sheet.

from openpyxl import Workbook
from openpyxl import load_workbook
from date time import date

workbookName = "Excel workbook.xlsm"
wb = Workbook()
wb = load_workbook(workbookName, data_only=True, keep_vba=True)
ws = wb["Sheet1"]
rowCount = 2000 
insertRow = rowCount + 7

origDate = date.today()
dateString = datetime.datetime.strftime(origDate, '%m/%d/%Y')
insertDate = datetime.datetime.strptime(dateString, '%m/%d/%Y').date()
dateCell = ws.cell(row = insertRow, column = 1)
dateCell.value = insertDate

wb.save("Excel workbook.xlsm")

So for example, if I ran this code using today's date of 03/19/2021, the cell would look like 2021-03-18 with General type.

Not sure what I am missing, but I want the inserted cell to have 'Date' type in 'mm/dd/yyyy' format. Any pointers?

Nathan Anderson
  • 155
  • 1
  • 2
  • 7

1 Answers1

3

I think this can be done simply with:

dateCell.value = origDate
dateCell.number_format = 'mm/dd/yyyy'

Note that there's no such thing as a date data type in Excel. A date is just a formatted number. "Date" (and "General" for that matter) are just formatting; the underlying value of the cell is separate and unchanged.

Though if you really want the cell to show as having a "Date" format and not "Custom", perhaps:

dateCell.number_format = 'mm/dd/yyyy;@'
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • That works for formatting, but the data type of the cell is listed as Custom when I want it to be Date. Do you know how I could format the writing so the type is Date? – Nathan Anderson Mar 19 '21 at 18:29
  • @NathanAnderson - I don't see why that's necessary but see the edit. There's no such thing as a Date data type. There's just date formatting. A date is a number. – BigBen Mar 19 '21 at 18:34