Spreadsheet custom column dates dd-MM-yyyy

Question

I have a Spreadsheet and before the data is received by the Spreadsheet I am adding this format: 'dd-MM-yyyy HH:mm:sss' so that the column can come in with a Short Date in an Australian format (Day / Month / Year).
Image_9086_1721279399115
For reference this is the DateTime on the object before it is formatted.
2023-09-08 06:03:00.000
Image_1284_1721279283640
It is then formatted in my Spreadsheet as 08-09-23 6:03:0 correctly doing dd-mm-yy for some reason yyyy ends up as yy which is another more minor issue.
Image_4600_1721279306206

In the formula bar this is also shown as 9/8/2023 6:03:00 AM and when I double click the cell and enter into editing mode this is also the displayed data.
Image_5988_1721279317902
Image_2337_1721279338756
So now I have month first but when I make this edit and update the cell on cellSave it reverts back to the dd-mm-yyyy but then includes the AM/PM 18/9/2023 6:03:00 AM
Image_2797_1721279353465
There is no extra code for this column on the cellSave the object is detected on the event as explained.
Image_5926_1721279518525

Is there anything to ensure that my displayText and value are aligned while using a dd-mm-yyyy format?

I can't find anything in the documentation for this.

Much appreciate in advance as this has been troubling me for quite some time and feel like I would potentially run into similar issues with other areas of Syncfusion.

MCR

Janakiraman Sakthivel · Answer

Hi Myles Renaud,Please find the details regarding the reported queries below.S.NoQueriesComments1In the formula bar this is also shown as 9/8/2023 6:03:00 AM and when I double click the cell and enter into editing mode this is also the displayed data.When the spreadsheet is in the default English culture, if you apply a custom date format such as 'dd-MM-yyyy HH:mm:sss' to the spreadsheet, only the display text of the cell will update according to the applied custom format.However, the value displayed in the formula bar and the value of the cell after editing will remain in the default English culture's short date format (dd/mm/yyyy). This is not an issue on our end, it is the standard behavior of MS Excel, and we have implemented the same behavior in our EJ2 spreadsheet component.For your reference, we have attached a video demonstration of the behavior in MS Excel below.Video: Please see the attachment. 2It is then formatted in my Spreadsheet as 08-09-23 6:03:0 correctly doing dd-mm-yy for some reason yyyy ends up as yy which is another more minor issue.We have created a sample and assigned the value '2023-09-08 06:03:00.000' in cell 'E2' and applied the format 'dd-MM-yyyy HH:mm:sss' to that cell to replicate your problem.On our end, the "yyyy" in the applied format is maintained correctly in the "format" property and in the display text of the cell. And we were unable to replicate the reported problem.  Please check the video we’ve shared below for your reference. For your reference, we have attached the created sample below.Sample: Please see the attachment. 3. Now I have month first but when I make this edit and update the cell on cellSave it reverts back to the dd-mm-yyyy but then includes the AM/PM 18/9/2023 6:03:00 AM.Is there anything to ensure that my displayText and value are aligned while using a dd-mm-yyyy format?We have checked this issue on our end but were unable to replicate it. In the cellSave event's argument, the displayText appears as '18-09-2023 06:03:00' without AM/PM and in the correct "dd-MM-yyyy" format on our end. Please check the video we’ve shared below for your reference.Therefore, to proceed further and replicate your second and third queries on our end, please provide us with the following details: Please confirm how you applied your custom format to that cell and provide detailed information? And please share the code snippet used for this to help us proceed further.Please share the complete client-side code snippets for our reference. And if you have made any customizations, please share the complete customization code along with this.Please share a video demonstration of the reported problems for better understanding.Please share the Essential Studio product version in which you are facing the reported problem. These details will help us understand how you maintained and loaded the data on your end, allowing us to replicate your reported problems based on our end and provide a quicker solution.Regards,Janakiraman S.Attachment: Sample_and_Video_452c2033.zip

S.No	Queries	Comments
1	In the formula bar this is also shown as 9/8/2023 6:03:00 AM and when I double click the cell and enter into editing mode this is also the displayed data.	When the spreadsheet is in the default English culture, if you apply a custom date format such as 'dd-MM-yyyy HH:mm:sss' to the spreadsheet, only the display text of the cell will update according to the applied custom format. However, the value displayed in the formula bar and the value of the cell after editing will remain in the default English culture's short date format (dd/mm/yyyy). This is not an issue on our end, it is the standard behavior of MS Excel, and we have implemented the same behavior in our EJ2 spreadsheet component. For your reference, we have attached a video demonstration of the behavior in MS Excel below. Video: Please see the attachment.
2	It is then formatted in my Spreadsheet as 08-09-23 6:03:0 correctly doing dd-mm-yy for some reason yyyy ends up as yy which is another more minor issue.	We have created a sample and assigned the value '2023-09-08 06:03:00.000' in cell 'E2' and applied the format 'dd-MM-yyyy HH:mm:sss' to that cell to replicate your problem. On our end, the "yyyy" in the applied format is maintained correctly in the "format" property and in the display text of the cell. And we were unable to replicate the reported problem. Please check the video we’ve shared below for your reference. For your reference, we have attached the created sample below. Sample: Please see the attachment.
3.	Now I have month first but when I make this edit and update the cell on cellSave it reverts back to the dd-mm-yyyy but then includes the AM/PM 18/9/2023 6:03:00 AM. Is there anything to ensure that my displayText and value are aligned while using a dd-mm-yyyy format?	We have checked this issue on our end but were unable to replicate it. In the cellSave event's argument, the displayText appears as '18-09-2023 06:03:00' without AM/PM and in the correct "dd-MM-yyyy" format on our end. Please check the video we’ve shared below for your reference. Therefore, to proceed further and replicate your second and third queries on our end, please provide us with the following details: Please confirm how you applied your custom format to that cell and provide detailed information? And please share the code snippet used for this to help us proceed further. Please share the complete client-side code snippets for our reference. And if you have made any customizations, please share the complete customization code along with this. Please share a video demonstration of the reported problems for better understanding. Please share the Essential Studio product version in which you are facing the reported problem. These details will help us understand how you maintained and loaded the data on your end, allowing us to replicate your reported problems based on our end and provide a quicker solution.