Error When reading a dates from Excel

[abdel (Robot usa)]

Hello,
Here is the scenario:
1-Read the range with workbook read range( i have no choice).

the date columns is “Date de
la facture”
19.07.2021
10/02/2020
13/08/2020
11/09/2014
02/09/2021
12/04/2019
26/03/2021
13/07/2018
03/06/2021
25/01/2019
22/09/2021
23/09/2021
26/01/2015
26/04/2021

2- i need the dates in format dd.MM.yyy, but i got the following error:

sample.xlsx (8.8 KB)

[ignasi.peiris]

Good afternoon @abdel ,

I did try with the data you wrote, and I can identify a total of 4 possible formats, meaning that the ParseExact has to know that 4 formats:

{"dd.MM.yyyy","dd/MM/yyyy hh:mm:ss","dd/MM/yyyy","MM/dd/yyyy hh:mm:ss"}

You can define all possible formats in an array, and then send them to the ParseExact in the following way:

Example:

Assign1:
var_Formats = {"dd.MM.yyyy","dd/MM/yyyy hh:mm:ss","dd/MM/yyyy","MM/dd/yyyy hh:mm:ss"}

Assign2:
DateTime.ParseExact(CurrentRow(7).ToString,var_Formats,new CultureInfo("en-GB"), DateTimeStyles.None).ToString("dd.MM.yyyy")

Code Example:
TestDateFormat.zip (62.8 KB)

Output:

Hope this helps!

Best Regards,
Ignasi