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

[dgreen (Daniel Green)]

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.

[Manish540 (Manish Shettigar)]

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

[lakshman (Ganta lakshman)]

@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.

[Palaniyappan (Palaniyappan P)]

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

[dgreen (Daniel Green)]

@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?

[Palaniyappan (Palaniyappan P)]

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

How to Use Select Method

So i have scraped Invoice from Acme Website and now i want to use Select(Filter) to get the invoice of data Greater than 7-1-2018 so how do i use select to only get the above given condition Thanks in Advance

Filter Datatable contains datetime

Hi, I have an excel sheet where Robot Start Date column is in General format. Its supposed to contains values in datetime format MM/dd/yyyy HH:mm:ss I would like to filter the datatable to create a second datatable for rows which have the same date MM/dd/yyyy I’ve tried Dt.Select(“[Robot Start Date] LIKE ‘%MM/dd/yyyy%’”).copytodatatable and it says cannot perform operand LIKE on system.string and system.datetime I cannot use filter datatable activity because it is not a table in the Excel sh…

Cheers @dgreen

[dgreen (Daniel Green)]

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?

[Palaniyappan (Palaniyappan P)]

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

Cheers @dgreen