You can convert the date to the format you want using the FormatDate built-in action.

Paul Davies wrote:

As João Melo mentioned, at the moment you need to format before writing to excel. An upcoming version will have the option to set the number format on a cell.

Paul Davies, have you been able to use the NumberFormat property when writing data to solve your issue?

I have a follow up question: when formatting the date using FormatDateTime(TextToDate(InvoiceItem.InvoiceDetails.Current.Charges.Current.ToTime), "MMM.dd.yyyy") so that it gets outputted to Excel like: Sep.03.2021 seems to make Excel think it is text. 

I can set the cell to date and choose a date format of course, but that doesn't work. Using the FormatDateTime(TextToDate(theDate), "M/d/yyyy") does work, though.

Anyone got a way around this?

I also had a problem, when trying to format date time they pointed it out to me, my solution was to perform the conversion using the Substr() function, doing direct manipulation of the text.

In my specific case, I had a text 20201020 (yyyymmdd) and needed to convert it to dd/mm/yyyy, so I did the following operation:

Substr(DateText, 6, 2) + "/" + Substr(DateText, 4, 2) + "/" + Substr(DateText, 0, 4)

The AdvancedExcel library now supports storing data as date or date/time in an Excel cell while also having excel display it in a particular format.

Here is how:

1. Use AdvancedExcel's Cell_Write action (the example action below just wraps it)
2. In CellValue, format the date/time value as "yyyy-MM-dd HH:mm:ss", or if it is just date then "yyyy-MM-dd". This is not the display format, just the way to ensure the value is passed to Excel correctly.
3. Set the cell type to "date" or "datetime" depending on your needs
4. Then set the NumberFormat (under CellFormat) 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