How to know whether the date column in excel is having dates in correct date format (YYYY-MM-DD)

Hi Folks ,
Hope all are doing well and safe at home.

So I have a task in which I have to read an excel , which contains 1 column - “Date”

image

Second thing , after reading that excel ,

1.) Bot should through a message in a message box, if the date is not in correct date format , i.e., YYYY-MM-DD . Warning message in message box can be like: Date is not in correct format , Please use YYYY-MM-DD format .

Note: The warning message should also tell that which row has incorrect date format. Example : Date is not in correct format , Please use YYYY-MM-DD format in row 3

I am attaching workflow screenshot for reference :


Please have some time on this and help me if you have some idea related to this

@rishabhverma
In general you can do it like this:

  • iterate over all rows / values
  • Evaluate the date

Evaluation can be done on

  • string level (Regex, contains Letter…)
  • date Conversion level
    • DateTimeParse/ParseExact Method and thrown Exception (string is not valid date Error Message)
    • Some members are working with DateTime.TryParse Function as well
1 Like

Hi @rishabhverma,

The approach I can think of is:

  1. Call a FOREACH activity to loop the data table.

  2. In the FOREACH, call a TRY CATCH activity.

  3. In the TRYCATCH activity, call a LOG MESSAGE activity and enter the following:
    "Date from Excel: " + DateTime.ParseExact(row("Date").ToString,"yyyy-MM-dd",System.Globalization.CultureInfo.InvariantCulture).ToString

    Note: The above will error out if the date format is not in yyyy-MM-dd. Not sure if you need the date value further in your process but I used a Log Message Activity because the goal is to test if the date is in yyyy-MM-dd format.

  4. In the catch part, add System Exception. Then call a Message Box and enter the message:
    "Date is not in correct format. Please use YYYY-MM-DD format in row: " + Convert.ToString(readDataFile1.Rows.IndexOf(row) + 1)

It should look like this:

1 Like

Ok , let me check this.

@jcastro , Hey
So I have done as you guided me , but the issue is I am getting the error message for the correct date as well (date is not in correct date format in row 1) , which is actually not expected.

This is the try block -

This is the catch block -

This is the error message which is shown for first row , which actually have date in correct format.
image image

Hi @rishabhverma,

In your Read Range activity, can you select/toggle the PreserveFormat checkbox?

image

Then try running the bot again :slight_smile:

1 Like

done , now it is working fine,but what this preserveFormat means ?? Also I marked your answer as Solution, Thanks

The PreserveFormat helps the Read Range retain the format of values from the Excel file. The reason why the Message Box was being displayed for the entry with the correct yyyy-MM-dd format is because UiPath reads the date value as M/dd/yyyy. If you look in your Excel file, and focus on the one with the correct entry, you would see in the Formula box that the format is “changed” to M/dd/yyyy:

image

Once the PreserveFormat is toggled, it tells the bot to get the date value as is. :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.