python: pasting a date in 'dd/mm/yyyy' to excel converts it to 'mm/dd/yyyy'

Question

I am trying to paste a date using python code onto an excel cell

ws.Range('A1').Value = '11/06/2020' i.e. 11 June 2020

and what I see in that excel cell is 06/11/2020 i.e. 6th of November 2020.

Any clues what is going on?

Running this on my windows, same code on my Mac runs fine!

codes, if anyone interested

import win32com.client
from datetime import datetime
xlapp = win32com.client.DispatchEx("Excel.Application")
xlapp.Workbooks.Open('C:\\blp\\API\\Office Tools\\BloombergUI.xla')
xlapp.RegisterXLL('C:/blp/API/Office Tools/bofaddin.dll')
filepath = give the path here
wb = xlapp.Workbooks.Open(filepath,None,False)
xlapp.Visible = True
wb_addin = ('C:/blp/API/Office Tools/bofaddin.dll')
ws = wb.Worksheets[‘AB’]
ws.Range('A18').Value = '11/06/2020'

additional comments: btw feels like its an issue when you use win32com. I just tried using openpyxl and it worked fine. unfortunately I have to use BBG addins in my excel and that works the best with win32com it seems

Excel will parse the string according to `short date` **Windows Regional Settings** of the computer on which it is running. So your windows computer likely has `MDY` for its short date setting. I'm not into Python, but [this to convert to an Excel serial number](https://stackoverflow.com/questions/9574793/how-to-convert-a-python-datetime-datetime-to-excel-serial-date-number) might be helpful. — Ron Rosenfeld, Jun 11 '20 at 11:43

score 1 · Answer 1 · answered Jun 11 '20 at 11:48

1

Do format the DateTime or apply the format to your worksheet because based on the regional DateTime settings it will differ like MDY or DMY

If you're using xlsxwriter writer engine use below code:

formatdict = {'num_format':'mm/dd/yyyy'}
fmt = workbook.add_format(formatdict)

Please add your sample code or post some more information about your libraries.

answered Jun 11 '20 at 11:48

Parvathirajan Natarajan

1,240
15
29

I check its DMY (my comment above) , I am using win32com. will your code work on that as well? – user13673013 Jun 11 '20 at 11:51
what package you're using to write excel? – Parvathirajan Natarajan Jun 11 '20 at 11:51
btw feels like its an issue when you use win32com. I just tried using openpyxl and it worked fine. unfortunately I have to use BBG addins in my excel and that works the best with win32com it seems – user13673013 Jun 11 '20 at 13:19

score 0 · Answer 2 · answered Jun 11 '20 at 11:51

There is differents date format e.g dd/mm/yy, mm/dd/yy, etc... your version of excel probably uses this format mm/dd/yy, to convert dd/mm/yy format in mm/dd/yy you can try this:

date1 = '22/7/1'
d_excel = date1.split('/')
d_excel[0], d_excel[1] = d_excel[1],d_excel[0]
print('/'.join(d_excel))

python: pasting a date in 'dd/mm/yyyy' to excel converts it to 'mm/dd/yyyy'

2 Answers2