Date format: Read range as dd/MM/yyyy and write range as MM/dd/yyyy

Hi - I’m receiving an excel file with dates in a european format (dd/MM/yyyy) and need to read all the data in the file, process data in a table and write to an excel workbook. During this i need to convert the date format to US format (MM/dd/yyyy) for two columns in the range. Can anyone help? I see similar topics but mostly addressing date as a variable not a range.

Use this code to change to your format,
“CDate(EuropeanDate.ToString).ToString((“MM/dd/yyyy”))”

@dgreen

I guess it’s changing in excel side.

Do one thing create one Excel template file with predefined column type and then write data into Excel file using Write Range activity. This should work.

Yah that’s possible
Get the datatable with a variable named dt from read range activity
—now use a for each row loop and pass the dt as input
–inside the loop we can use assign activity like this
row(“yourcolumnname”) = “‘“ + DateTime.ParseExact(row(“yourcolumnname”).ToString.SubString(0,10),”dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToSring(“MM/dd/yyyy”)

Here we are actually going concatenating a single quote along with the date so that format can we want can be retained
Hope this would help you
Cheers @dgreen

1 Like

@lakshman @Manish540 @Palaniyappan I used the ‘For each’ solution provided by Palaniyappan but realize I now have an additional challenge. The process requires filter on one of these dates. The data set is filtered based on “start date” - can I convert from European date to US but keep date format instead of string so my filter wizard can still work?

1 Like

Fine to filter the date in a datatable kindly have a view in this thread

Cheers @dgreen

Hi Palaniyappan, I’m still having a challenge here. After converting european date to string (“MM/dd/yyyy”). I then need to filter for dates earlier than last day of prior month. I am using a variable (New datetime(now.Date.Year, now.Date.Month,1).AddDays(-1)) to give me the last day of prior month. When filtering the datatable I need to return records with the converted date to less than this variable. However now that they are not in the same format (String vs DateTime) this filter not working. Having the date converted to string is not enabling this. Is there a way to convert european DateTime to US DateTime?

1 Like

Did this Kind of expression helped us on Yourdatatable = Select("[datecolumn] >= #" + from_date + “# AND [datecolumn] <= #” + to_date + “#”).Copytodatatable()

Cheers @dgreen