Hello, everyone.
I'm using the Excel Package Forge component for creating an Excel workbook with 3 different sheets. Everything works and exports as expected, except for the dates. In the structures I'm using the Data Types are set to Date, but whenever I export and open the excel file the dates show as following:
I've noticed that if I select the dates in the Excel, click the Format tab on top and Format to Date, it shows the correct that, which means it recognizes the dates. Yet, I don't know how to make it show the actual date whenever I export the file, instead of showing the numbers like they are in the above picture.
Thanks for your time and help,
Gonçalo Morais
Hi Gonçalo,
on the date column is it possible to mark those columns as text?
If not, what about forcing the date column is in a date format know by Outsystems?
Best luck
I'm not sure it is possible with Excel Package because it doesn't expose the number format.
I have used this on a few projects using AdvancedExcel (which looks like it was copied from Excel Package, as when you install either of them, OutSystems thinks it is the same forge component and removes the other one).
With AdvancedExcel it is possible to format a date or date time in Excel, and also for Excel to consider a date or date/time cell.
Use AdvancedExcel's Cell_Write (the function below just wraps it)
To format the date in Excel, you need to
Format the date/time value as "yyyy-MM-dd HH:mm:ss", or if it is just date then "yyyy-MM-dd"
Set the cell type to "date" or "datetime"
Then set the NumberFormat using an Excel number format (not a .NET number format) eg "d/mm/yyyy h:mm:ss AM/PM". It is strange that "mm" is used for both month AND minute, but Excel understands this somehow.
Here is a link on how to format numbers as dates or times in Excel.
I hope this helps!
Kind regards,
Stuart