Change date format in column

Hey everyone I have a column in a datatable where the format of date is d.MM.yyyy , I want to change it all values in this column to MM/d/yyyy and I cannot use for each activity because there are to many rows to take this action for each row. I might change the “.” with “/” using .Replace but I have no idea how to change the position of day and month.

I appreciate every bit of help, thank you

Hi @jntrk
Use the below method
Date.ParseExact(DT.Rows.item(“ColumnName”),“d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”)

1 Like

Hi @jntrk,

Try this below code

Cdate(yourvariable.ToString).ToString(“MM/d/yyyy”)

Thanks!

1 Like

Sorry for the late response, I encountered with this issue,

Hi @jntrk

U can try this to change the date format to MM/d/yyyy

Datatable_variable.AsEnunerable().ToList().ForEach(Sub(row) row(columnName)= Date.ParseExact(row(“ColumnName”),“d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

U can try this in a invoke code activitiy.

But when u write the datatable in excel it is better to add the data in text format rather than adding in datetime because of excel cell may be formated in one date format, like they may be formated in dateformat of dd.MM.yyyy but even when u paste the date in format of MM/dd/yyyy it will appear as dd.MM.yyyy.

Before writing the datatable to excel try to convert the date format in excel to.required one either manually or using Balareva activitiy.

Hope the information helps you

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed::relaxed:

1 Like

Hey NIVED,

Can you send me a xaml, of this I’ve worked on it but cannot resolve the errors.

Hi @jntrk

it seems i had done some mistakes from my side in the code
kindly try with this code
Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row(columnName)= DateTime.ParseExact(row(“ColumnName”).ToString, “d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

Regards,
Nived N
Happy Automation

Thank you for your fast response, I get the following.

Hi @jntrk

Check this

" -<System.Data.DataSetExtensions> - " is actually added in my main file but still the same issue.

Hi @jntrk

Just see the response I had edited it

yess finally I got rid of that problem, now I get this

Invoke code: Exception has been thrown by the target of an invocation.
Sorry for the troubles I am causing.

now I went over the datatable read range takes cell as “02/22/2020 00:00:00” etc. I want to change it to 22.02.2020,

currently the code is like this:

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row("DDate")= DateTime.ParseExact(row("DDate").ToString, "MM/dd/YYYY hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy"))

do you see any mistake?

did u change the did u put the columnname u need to change the date format in the invoke code

[instead of “columnname” that i had provided ]

also tick the preserve format option

I enabled preserve filter:

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row("DDate")= DateTime.ParseExact(row("DDate").ToString, "MM/dd/YYYY",System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy"))

its like this, the column is named “ddate” i switch it with your columnname variable as a string, is the one I wrote here is wrong?

@jntrk - Could you please show/share your input file screenshot??

image

@jntrk - Please try like this…

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row(“DDate”)= DateTime.ParseExact(row(“DDate”).ToString, “M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”))

Because when you have single digit months and dates it was written without prefixing 0…

yes wheen I step into debug, when I check the value inside the datatable_variable its 2/22/2020, I still get the same error tho :confused:

Invoke code: Exception has been thrown by the target of an invocation.

@jntrk - Could you please empty other columns and send/share the sheet with only date value on it??