Date format problem when writing dates from pandas dataFrame to excel file using .value and .number_format

[jellyslalsher]

OS (e.g. Windows 10 or macOS Sierra)

Im on Windows 10 enterprise edition

Versions of xlwings, Excel and Python

Latest stable version of xlwings with python 3.11.5 in anaconda and Excel Office 365 in french

Describe your issue (incl. Traceback!)

I'm trying to format two column to have the values show up as dates upon creation, initally they showed up as an int and when converted to date they gave the correct format date. but i want them to be in date format without having to do it manually in excel.

I tried all sorts of offered formatted functions and also formatted the data in the pandas dataFrame before insertion

the function below insert formulas in a sheet and that interacts with other sheets that have the data in them. So my sheet in this case rather serves as a reporting sheets for the "data warehouse" sheets

Traceback

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

def add_formulas_to_new_sheet(workbook):
    print("ENTRED THE FUNCTION add_formulas_to_new_sheet")
    if 'Ecarts PSR Register' in [sheet.name for sheet in workbook.sheets]:
        print("Sheet 'Ecarts PSR Register' already exists. No changes made.")
    else:
        new_sheet = workbook.sheets.add('Ecarts PSR Register')
        new_sheet.range('B7').formula2 = '=IF(C7="", "", VLOOKUP(C7&D7, PSR!A:B, 2, FALSE))'
        new_sheet.range('C7').formula2 = '=FILTER(REGISTER!A:B, REGISTER!M:M="OUI")'
        new_sheet.range('E7').formula2 = '=FILTER(REGISTER!H:H, REGISTER!M:M="OUI")'
        new_sheet.range('F7').formula2 = '=FILTER(REGISTER!D:D, REGISTER!M:M="OUI")'
        new_sheet.range('G7').formula2 = '=IF(C7="", "", VLOOKUP(C7&D7, PSR!A:J, 10, FALSE))'
        new_sheet.range('H7').formula2 = '=IF(C7="", "", VLOOKUP(C7&D7, PSR!A:H, 7, FALSE))'

        header_cells = ['B2', 'B3', 'B6', 'C6', 'D6', 'E6', 'F6', 'G6', 'H6', 'E5', 'G5']
        values = ['Ecarts PSR Register', 'Remonte les écarts sur les montants ou dates de facture entre le PSR et PSA (register)',
                  'Commande', 'BDC', 'Invoice ID', 'Pretax Amt Bill Curr', 'Date facture', 'Amount', 'Date Facture', 'REGISTER', 'PSR']

        for cell, value in zip(header_cells, values):
            rng = new_sheet.range(cell)
            rng.value = value
            rng.api.Font.Bold = True
            rng.api.Font.Size = 12 

        new_sheet.range('B6:D6').color = (88, 214, 141)

        new_sheet.range('E5:F5').merge()
        new_sheet.range('G5:H5').merge()

        new_sheet.range('B2').api.Font.Size = 15
        new_sheet.range('B3').api.Font.Size = 15

        for cell in ['E5', 'H5']:
            rng = new_sheet.range(cell)
            rng.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter
            rng.api.Font.Bold = True
            rng.api.Font.Size = 12
        new_sheet.range('E5:F5').color = (133,193,233)
        new_sheet.range('G5:H5').color = (187,143,206)

        for cell in ['E6','F6']:
            rng = new_sheet.range(cell)
            rng.color = (133, 193, 233)
        for cell in ['G6','H6']:
            rng = new_sheet.range(cell)
            rng.color = (187,143,206)
    new_sheet.range('B:H').columns.autofit()

    new_sheet.range('F7:F1234').number_format = 'MM/DD/YYYY'
    new_sheet.range('H7:H1235').number_format = 'MM/DD/YYYY'

    new_sheet.range('C:C').column_width = 25 

[fzumstein]

Please provide a minimal code sample that only reproduces the error instead of your whole script.

[jellyslalsher]

Here's where I believe the conversion of the data in the column to a the date format is failing new_sheet.range('F7:F1234').number_format = 'MM/DD/YYYY'