DateTime Format incorrect

DateTime Format incorrect

Posted by: qui.ha on 18 April 2023, 5:15 am EST

• 

  qui.ha

  • Post Options:
  • Link
    Copy

  Posted 18 April 2023, 5:15 am EST - Updated 18 April 2023, 5:21 am EST

  Dear SpreadJs Team,
  
  We got a issue relate to datetime format.

  1. I have a cell with a date format is d-mmm-yy
  2. Then the date was typed in as 19/7/22 and it was converted to the format 2019-July-22 but the expected is 19-Jul-2022
  3. I checked with Excel for Mac and it works properly
     
     
     

     2124×1736 358 KB
• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 19 April 2023, 2:48 am EST

  Hello,

  When the date “19/7/22” was entered in cell and “d-mmm-yy” format was applied, the date was not formatted as “19-Jul-22” because the ‘en-US’ culture is set to SpreadJS by default. It is important to note that the values are parsed based on the culture applied.

  Since in ‘en-US’ culture, the entered date string “19/7/22” is parsed in ‘yyyy/mm/dd’ format. That is why, in string “19/7/22” 19 is parsed as year 2019, 7 is parsed as month 7 and 22 is parsed as date 7.

  You can modify the DateTimeFormat.shortDatePattern property of CultureInfo object so that the string “19/7/22” is parsed in “dd/mm/yyyy” format. Similarly, you could modify the other properties of DateTimeFormat interface object of a CultureInfo object to parse time, long date etc differently. It is important to note that a CultureInfo object represents a culture and you can create and add custom cultures to SpreadJS.

  Please note that the value “19/7/22” is shown as “19-Jul-22” after applying ‘d-mmm-yy’ formatter in MS Excel because MS Excel takes the Culture information from the system. You may have applied a culture in your system that is parsing the date string as mentioned above.

  Please refer to the code snippet and attached sample for further understanding.

  // specifies how short date will be parsed when entered
  culture_en_IN.DateTimeFormat.shortDatePattern = "dd/MM/yyyy";

  Sample: https://jscodemine.grapecity.com/share/SH5mw7-C3k2YC3HX4dSBRQ/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Please let us know if you still face any issues.

  Doc references:

  CultureInfo class:https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Common.CultureInfo

  IDateTimeFormat interface: https://www.grapecity.com/spreadjs/api/interfaces/GC.Spread.Common.IDateTimeFormat

  CultureManager class: https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Common.CultureManager

  Regards,

  Ankit