Datatable date format has different format compared to source (.xlsx)

Help
, ,
1

Hello,

I read an excel file that has a date column. All the cells in this column are formatted the same, as in the screenshot below and windows date format is dd-MMM-yy

ExcelDate
ExcelDate361×525 9.5 KB

When I read the table using read range activity, the output format is MM/dd/yyyy for one cell, the date blue rectangle below.
Datatable
I need the date in dd/MM/yy format. Can someone tell how to do this, please?

2

Hi Victor,

Try this to format the date.

formatted_date_string = Datetime.ParseExact(your_actual_date_from_excel ,“dd-MM-yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yy”)

Have a good day :slightly_smiling_face:

3

Whether you get all the date formats in the form of “MM/dd/yyyy HH:mm:ss” then use below code,
DateTime.ParseExact((in_DateColumnName).ToString.Replace(“-”,“/”),“MM/dd/yyyy HH:mm:ss”, Globalization.CultureInfo.InvariantCulture).ToString(in_SpecificDateFormat)

4

Thank you for your answers!

It looks like, although the date format is set for that column, some of the dates are numbers and some are text. That’s a strange thing related to Excel, I guess. I tested it using the formula IsNumber.

To solve this issue, in Excel I converted the dates using Data>Text to Column. After that all cells were numbers and UiPath read the table correctly.