DAYS function not working with Finnish date format

Posted by: petri.rasanen on 13 December 2021, 9:07 am EST

    • Post Options:
    • Link

    Posted 13 December 2021, 9:07 am EST

    Hi,

    DAYS function seems to work only with US date format. I have tried different formatting in excel cells (B3:C8) but DAYS function just does not work with Finnish date format (dd.MM.yyyy). We have also Spread for WinForms, where Finnish date format works when regional settings is set to Finnish.

    Br, PetriTestFile.zip

  • Posted 20 December 2021, 12:28 am EST

    Hi,

    Spread for WinForms(and Excel) is a native application and has access to machine’s culture. But SpreadJS being a web application doesn’t have access to machine’s culture and hence we need to define the culture for SpreadJS.

    In order for days function to calculate days in Finnish Format, you need to define the culture for the Finnish Format.

    We have defined the culture for the Finnish Date format in the following sample. Please verify with the excel sheet provided, and let us know if you face any other issue.

    Also, upon investigation, we found that the date stored in your excel file is of “string” type which is causing issues with the date formula, we suggest you define the format using culture-specific format types from excel dropdown.

    NumberFormatId Lookup Table: https://github.com/closedxml/closedxml/wiki/NumberFormatId-Lookup-Table

    Sample: https://jscodemine.grapecity.com/share/VtmBZ2eh8UCyizB5NyW9iA/

    Sample Excel File: test1.xlsx

    Thanks

  • Posted 29 December 2021, 10:16 am EST

    Hi,

    I have set the culture information for SpreadJS but that does not help. Here is a sample project with GCDocuments. We open the excel file with GCDocuments and convert it to json to be shown in SpreadJS. Can you please implement the solution to this sample how to get DAYS function to work with Finnish date format.

    Br,

    PetriDAYS-function.zip

  • Posted 3 January 2022, 2:30 am EST

    Hi,

    In the shared sample, the issue is arising because the date stored in your excel file is of “string” type which is causing issues with the date formula, we suggest you define the format using culture-specific format types from excel dropdown.

    Please refer to the following sample where we have attached an excel file with the date in the correct format. Just replace your excel file with the new excel file. SpreadJS recognizes it as date and evaluates the days function.

    Regards,

    Avinash

    DAYS-function1.zip

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels