How to convert a string to date

[Chendoran_M]

Some columns in my excel sheet must be in date format but its in string format(“19940331”), how do I convert this data into date format(“dd/MM/yyyy”,ie.“31/03/1994”) and write it back to the excel sheet.
When I tried to convert I got this error, “String ‘19850301’ was not recognized as a valid DateTime.”

[Gokul001 (Gokul Balaji)]

HI @Chendoran_M

You can try with Format Cell activity to change the format of the date

Check out this thread

How to change date format in excel file?

Hi @SURESH_P Go to → Activities Panel → Click on Filter → Select Show Modern. [image] Step 1 : Use Excel process scope followed by Use Excel file activities. Step 2 : Use Format Cell Activity, You can try to change the format of the column Excel process scope activity Use excel file activity Format cell activity [image] Regards Gokul

How about this expression

DateTime.ParseExact(YourInput.ToString,{"yyyyMMdd","yyyyMMd"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("dd/MM/yyyy")

Regards
Gokul

[Yoichi]

Hi,

Can you try the following expression?

DateTime.ParseExact(yourString,"yyyyMMdd",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

Regards,

[Umadevi_Sanjeevi]

@Chendoran_M

19940331

Hi @Chendoran_M ,

Can you try this

str1 = “19940331”

out = DateTime.ParseExact(str1,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”)

Output Will be

31/03/1994

Thanks!

[Chendoran_M]

I am still facing the same issue, “Assign: String ‘UiPath.Excel.CurrentRowQuickHandle’ was not recognized as a valid DateTime.”

[Yoichi]

Hi,

It’s necessary to indicate column name as the following.

DateTime.ParseExact(CurrentRow("ColumnName").ToString,"yyyyMMdd",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

Regards,

[Chendoran_M]

Assign: The DateTime represented by the string ‘00000000’ is not supported in calendar ‘System.Globalization.GregorianCalendar’.

[Yoichi]

Hi,

Is your string “00000000” correct date data?

Regards,

[Rahul_Unnikrishnan]

Hello @Chendoran_M
You can use the Modify Date activity.

DateTime.ParseExact(strVar, “yyyymmdd”, Globalization.CultureInfo.InvariantCulture)

Thanks

[Chendoran_M]

It’s not date data. Its a string data.

[Yoichi]

Hi,

We would like to know if input string is “00000000”, what is result? Is it “00/00/0000”?
If so, it’s better to use regex instead of DateTime.Parse, as the following.

System.Text.RegularExpressions.Regex.Replace(CurrentRow("ColumnName").ToString,"(\d{4})(\d{2})(\d{2})","$3/$2/$1")

Regards,

[Chendoran_M]

After conversion the data are incorrect.
This is my data set, similarly I have multiple columns in the same sheet.
My problem here is, the data are in this form;

“The number in this cell is formatted as test or preceded by an apostrophe.”
Let’s say I have similar data form in 10 columns in the same sheet, now I need to convert these data to date format.

YearData.xlsx (8.7 KB)

[Yoichi]

Hi,

If you need to have data recognized as datetime and display it as dd/MM/yyyy style, perhaps you should set cell format as dd/MM/yyyy then write date as yyyy/MM/dd style as the following.

Sample20221212-4.zip (15.8 KB)

Regards,

[Chendoran_M]

Got it!
Thank You.