Parse date into a single format

Hi All,

I have a excel file which has column “Refer Date”, the number of rows in the excel file could be 500k-800k~.

The format in the date column is very inconsistent

examples of date which are present in “Refer Date” column are mentioned below(it consists many formats like M/D/YYYY, MM-D-YYYY, OADate format etc. refer below)

3/2/2022, 44595, 28/FEB/2022, 02-3-2022, 01-03-202, 22-02-2022, 14/02/2022, N/A, 2022-01-06-, 2022-01-12T17:56:21Z.

I want to convert them into a single consistent format like MM/DD/YYYY

Any help is appreciated.


we can make use of the following building blocks:


Parsing many formats in one go
ensure the following:

arrFormats = {“dd/MMM/yyyy”, “dd-MM-yyyy”, … your other formats}

DateTime.ParseExact(YourString, arrFormats, cultureinfo.InvariantCulture, DateTimeStyles.None)

feel free to adapt the culture info

Finally we can check which case we do have to handle e.g. with regex
\d+ → OADate
NA → not parseable

Bringing all together we can parse the date and harmonize the format

Datetime.ParseExact(Refer Date).tostring(“MM/DD/YYYY”)

HI @Dhruvi_Arumugam

How about this expression?

DateTime.ParseExact(YourInput.ToString,{"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss","dd.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(“MM/dd/yyyy”)

You can simply add the any number of date formats inside the {“”,“”} and the formats should be separated by comma

The above expression will look into array of string and choose the apt format as per the input string and convert them and return you the output of desired format.


Hi @ppr

Thanks for the reply, but specific to OADate you mentioned we can you use regex, can you please elaborate on that how can we use it in expression, for N/A we can leave it.

Also, how can be use it on all of the data as we have 500k~ + rows.


can you share some sample data along with the column names?

Hi @Gokul001

Thanks for your reply.

How can I handle OADate format? and as I have 500k~ + rows how can i incorporate that change in all.


HI @Dhruvi_Arumugam

In this case you can try with Format cell activity to get the desired format.

You can try to change the format of the column

  1. Excel process scope activity
  2. Use excel file activity
  3. Format cell activity


Check out this XAML file

FormatCellStudioX.xaml (8.5 KB)


Try with BalaReva it will applicable only in Window Legacy

Check out the thread


Hi @Gokul001

Thanks! I will try this.


Hi @ppr

Apologies for late reply

Please find attached sample
Sample.xlsx (3.5 MB)

Below are the Columns in the file attached:

Closing date,Team,NCI Value,SA Name,Change Record (CR) #,Reference Date

The highlighted columns are the date columns.

Please let me know for any questions.


@ppr Can you please suggest I provided the sample.


Hi @ppr

I provided the sample data.

Can you please have a look here and provide some insights.


Hello @Dhruvi_Arumugam

Here one option is to use the Format cells activity with a loop. But if the format is static for the excel, then better you can create an Excel template with the required formats to the columns and then write the datatable to that excel. It will help to preserve format for all the columns as per our requirement.


1 Like