Changing the Custom Date Format

Hi Everyone,

Can some one please help me to understand, how to change the date format from excel.

Row values contains as below
In a cell it is 1/27/2020 0:00
But in formula bar 1/27/2020 12:00:00 AM
Confused which one to consider?

how to convert the above format to “dd/MM/yyyy”

Thank you so much for your kind help in advance.


Hi @winningvish

In excel the cell shows the value with the applied format and the formula bar shows the exact value. So, if you do the read range with preserved format enabled it will give you the value shown in the cell. If not it will give you the exact value which is shown in the formula bar.

Either way, you you can do a read range and get the values to a datatable.
Next, use a for each row activity and loop through the rows and extract the value to a date time variable.

Next, you can convert it to the format you mentioned and assign it back to the data row…

To convert the format of the date time variable, you can use variable.ToString(“dd/MM/yyyy”) code


Hi @Lahiru.Fernando,

Thank you very much for your kind explanation.

By Using DateTime.ParseExact method solved my query.

First I have used the log message in for each row to see how the bot will print the values from Excel and understood the Date Format and used the DateTime.ParseExact. and it solved