Convert text to date format for Excel

Help Studio
1

Hello everyone,

I receive a date from SAP, which I transfer to an Excel table, and I would like to have the SAP date formatted as an Excel date.

According to the forum, it should be recognised with

“DateTime.ParseExact(CurrentRow.Item(“Date”).ToString, “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(“dd.MM.yyyy”)”.

work, but the cell format still remains in the Excel spreadsheet as standard, why is that?

image

2

That is because the datatable column Datum is still containing string hence the .ToString in your code
“DateTime.ParseExact(CurrentRow.Item(“Date”).ToString, “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(“dd.MM.yyyy”)”

Two ways to solve this. One way is to create a datatable containing a column Datum of datatype DateOnly (of course with other columns as in your data). Then write to excel.

Second way is to just create an excel template with the Datum column pre-formatted as Date. Then write your datatable to the template and save as another excel (so as to keep the template reusable).

3

Hi @NHoe ,

Try using the ‘Format cell’ activity. This would help you to change the data type format in selected range.

If the data is in column g for example , you can provide “G:G” as range or you can make use of the + as well. To set the data type, you can use the set format data type peoperty available in this activity.

Refer the official documentation of the activity,

If you need any further assistance in Configuring or if you are facing any challenge using this activity let me know happy to help out.

Happy automating.!

Thanks,
Gautham.