How to change DateFormat in UIPath

Hi Team,

Need your help to change the Date Format of Excel Values(Dates are in different formats) to the format(DD-MM-YYYY)ExcelValues.xlsx (9.5 KB)

1 Like

Fine hope this expression would help you resolve this
—use excel application scope and pass the file path of the excel
—inside the scope use read range activity and get the output with a variable of type datatable named dt—the use a for each row loop and pass the above variable as input
—inside the scope use IF condition like this
row(“yourcolumnname”).ToString.Length.Equals(8) AND row(“yourcolumnname”).ToString.Contains(“/“)
If the condition is true it will get into Then part and mention like this
row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString,”M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Or if the above condition fails it will go to ELSE part where we need to use another IF condition
And mention the condition like this
row(“yourcolumnname”).ToString.Length.Equals(9) AND row(“yourcolumnname”).ToString.Contains(“/“)
And if the condition passes it will go to THEN part
and mention like this
row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString,”M/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Or it will go to ELSE part where we need to use another IF condition with condition like this
row(“yourcolumnname”).ToString.Length.Equals(10) AND row(“yourcolumnname”).ToString.Contains(“-“)
If the condition is true it will get into Then part and mention like this
row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString,”MM-dd-yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Or if the above condition fails it will go to ELSE part
And no need to mention any there In the last else part

Cheers @RajeshT

2 Likes

Hi @Palaniyappan ,
Thank you for the detailed explanation.
row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString,”M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
where should i mention the above condition ,in “Assign” activity or WriteLine?
If Assign activity how should i convert string to Date?
Appreciate your reply!

1 Like

I made a small change in the above expression
Kindly have a view on it
And this expression what actually does is it will concatenated single quote and a string variable so that it will retains the same format as text and not as general
—it won’t convert that to DateTime format and will be as string and it is better to mention with that single quote along with that string so that the date format won’t get changed
Else it will change the format that we don’t need

Cheers @RajeshT

This is Perfect @Palaniyappan.
Small help ,can we change the dates are displaying like below if the day/month is 1 digit can we convert the same to 2 digit.
Actual:
8/6/2019
8/9/2019

Expected
08/06/2019
08/09/2019DateFormatChange.xaml (12.1 KB)

Yah with this expression it actually does the same

row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString,”MM-dd-yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Like in all expressions
Cheers @RajeshT

Hi @Palaniyappan ,

Could you please check xaml and excel sheet,its still getting same format.DateFormatChange.xaml (12.1 KB) ExcelValues.xlsx (10.3 KB)

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