I am using OLE2 objects to pass some data into an excel file.
This includes dates specifically in the 'dd-mm-yyyy' format.
Now, excel understands if a date has been passed, and auto-converts the value into the date format. By default, this is dd-mm-yy.
So I set the NumberFormats property of the column to 'dd-mm-yyyy'.
----
CALL METHOD OF O_EXCEL
'COLUMNS' = O_COLUMN
EXPORTING
#1 = GV_COL.
SET PROPERTY OF O_COLUMN
'NUMBERFORMAT' = 'DD-MM-YYYY'. "'[$-14409]dd-mm-yyyy;@'.
----
This works, but because my excel is localized to the US, it auto-converts it to MM-DD-YYYY. But it doesn't do this consistently.
When I pass 04-07-2017 (4th July), I get 07-04-2017
But when I pass 13-12-2017 (13th Dec), I get 13-12-2017.
So when the date doesn't quite add up in the mm-dd format, excel doesn't auto-convert it. Passing '@' as the NumberFormats value got me a 5 digit number, and I cannot concatenate a ''' to pass it as text because the file needs to be uploaded elsewhere and a ''' in the date column may cause issues.
Is there a way to fix this by disabling excel auto-conversion or formatting?
