Invalid Date Format

Help Studio
1

Hi all,
I am trying to read an excel worksheet (in Workbook A) using “Read Range” in Excel Application Scope into a datatable and then “Write Range” into another worksheet (in Workbook B).
However, there is a column in the source worksheet in which users might enter the date in the wrong format. This causes an error during “Write Range”.

Capture

Main.xaml (10.3 KB)

I would like to seek help to do the following:

  1. After reading the source worksheet, how can I loop through all the cells column named “Promised_Date”.
  2. For any invalid dates found, I would like to delete this cell in the datatable and make it blank.

Appreciate any help. Thank you.

2

If the date format in the excel cell is unreliable due to human error, than you need some vanlidation first before considering it is a valid date.

This is a ‘catch 22’ if you can’t read it due to invalid formatting. A solution could be to read the calls containing potential dates forcefuly as a string, or maybe a generic value type. That way you have your value in an internal variable at least, which you can then validate within your own proper error handling. (try DateTime.ParseExact)

If parsing the string → date fails, you can then trigger your action to delete/replace the value.

3

I have run into this. I start by building a datatable with the column headings I need. I would format your column in the Dt to DateTime for the dates

Then, in a for each for datatable loop - for each row assign a variable to the date column such promiseDate = row.item(“your column header”).toString

Then in another assign convertedPromiseDate = Conver.toDateTime(promiseDate)

Do an Add Data Row with the array {row.item(“Column1”).toSTring, etc except for the dates add the variable you created “convertedPromiseDate”. Then write the range to your new excel with the dt you created.

I hope that makes sense

Chris

4

You still also have to account for the human factor. In general people are idiots. (No offense). If Excel gives them the liberty to enter invalid data they will at some point. People that will write ‘a.s.a.p.’ of ‘unknown yet’ in a datefield. If the excelsheet does not use any data validation (which it should imho if it is used for RPA) you ‘will’ get exceptions on it. Because… well… people are idiots.

5

True to your statement that someone could write asap or next week instead of a date. In that case I would use an if statement after reading the cell and do a validation with an assign

Date.TryParseExact(str_datetime.ToString.SubString(0,10),“dd/MM/yyyy”,Nothing,Globalization.DateTimeStyles.None,Nothing)

And if nothing then delete