I'm afraid I can't setup a perfect repro, nonetheless I hope I can receive some hints.
My app server is in UK with UK settings and the final users I want to deliver this enhancement to are in USA with USA settings.
So I try to facilitate date type conversion by formatting the date field/column in Python as follows
d.strftime('%m/%d/%Y')
Then I write the xlsx using a format
format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write(row, col, item[0], format3)
When I open the xlsx and try to filter by date, the column is not correctly recognized as a date by Excel. Is this an issue only for me in the UK or also for my users in USA?
I've seen the answer, now I have a more difficult question. Let's say I'm in UK. If I save a cell as date in Excel, when my users open it in the USA, they see the cel in their localized format.
How can I achieve it in python xlsx?
This is what I've tried
import xlsxwriter
from datetime import date
mydate = date(2021,12,29)
path = 'C:\\my\\path\\test.xlsx'
wb = xlsxwriter.Workbook(path)
format = wb.add_format({'num_format': 'dd/mm/yyyy'})
ws = wb.add_worksheet('mydates')
ws.write(0,0,'date')
ws.write(1,0,mydate, format)
wb.close()
When I open it in excel it says it's a date, not customized.
But when I launch from a cmd control international and choose a different language format (let's say I switch from UK to USA) and I open the xlsx again, the format of the date is not transformed
I try to facilitate date type conversion
One simply does not try to "facilitate" date type conversion by passing a string in that way. The visualization format of a cell is completely disjoint from the cell content value, so keep your code (about format visualzation) with workbook.add_format and worksheet.write
format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write(row, col, item[0], format3)
I'd advice to delete that string formatting (in the content value):
d # .strftime('%m/%d/%Y')
It just works out of the box in xlsxwriter with the native Python vs Excel date types.
There is a magic trick to gain your bonus point. You have to use the Excel standard date format: it is the numer 14, E in hex .
format = wb.add_format()
format.set_num_format(0x0E)
Done!