DAYS function not working with Finnish date format

DAYS function not working with Finnish date format

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

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  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

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy

  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

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  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