Conditional formatting/formulas with dates

Conditional formatting/formulas with dates

Posted by: petri.rasanen on 10 July 2023, 7:37 am EST

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 10 July 2023, 7:37 am EST

  Hi,

  DATEVALUE formula used with Finnish regional settings is not working correctly with normal formula and formula used in conditional formatting. I have set the culture to use "myCulture.DateTimeFormat.shortDatePattern = “dd.MM.yyyy”
  
  I have attached excel file for testing. Cell C2 has conditional formatting and cell C4 has normal formula with DATEVALUE. This is working in Excel but not on SpreadJS (tested version 16.1).

  Br,
  
  Petri

  TestFileForGC.zip

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 11 July 2023, 11:42 pm EST - Updated 11 July 2023, 11:47 pm EST

  Hello Petri,

  We were not able to replicate the issue on our end. We tried to replicate the issue by importing the excel file provided by you. However, we did not encounter any issues. We set the "culture_en_FI.DateTimeFormat.shortDatePattern = “dd.MM.yyyy” as mentioned by you.

  Please refer to the attached sample.

  sample:https://jscodemine.grapecity.com/share/l8E-1mZ1OUKv477KY7B3mw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Also, you may try to change the defaultDatePattern to ‘culture_en_FI.DateTimeFormat.defaultDatePattern = “d/M/yyyy H:mm:ss”’

  If you still face the issue, please let us know and provide us with a minimal sample that replicates the issue. You may modify the sample provided by us.

  Regards,

  Ankit

  

• 

  petri.rasanen

  • Post Options:
  • Link
    Copy

  Posted 14 July 2023, 3:53 am EST

  Hi,

  Thanks for the code example. But in that you are using Designer. If you import the file to SpreadJS, it will happen. I tried to modify the example code but was not able to create let excelIO = new GC.Spread.Excel.IO();

  Br,
  
  Petri

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 17 July 2023, 7:52 am EST - Updated 17 July 2023, 7:58 am EST

  Hello,

  Please refer to the attached sample. In the attached sample, we set a culture to the Workbook with “myCulture.DateTimeFormat.shortDatePattern = “dd.MM.yyyy” and import the Excel file provided by you, however, we did not encounter any issues.

  Please refer to the attached sample and gif.

  sample: https://jscodemine.grapecity.com/share/W84aaloSi0OYdFOdZWVQNg/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Please let us know if we are missing any steps to replicate the issue on our end. Also, please provide us with a minimal sample that replicates the issue. This will help us to find the root cause of the issue and we would be able to help you accordingly.

  Regards