Excel Import Date Formatting

Help
1

Hi,
I’ve been having an issue with import dates from Excel, whenever I try to get the value of certain dates (using Read Cell/Range both result in same issue), I end up getting my dates in the MM/dd/yyyy format but want them to appear as dd/MM/yyyy.
To be clear, even when I use .ToString(“dd/MM/yyyy”) on the dates, they still appear in that format. I think the issue is most likely that they are being read as MM/dd/yyyy by UiPath, is there a way to change this? Since I’m not in the US, I don’t use that date format and have checked that they are set to appear as dd/MM/yyyy in Excel.

2

Hi @chrisL,

if your getting the dates every time in this format “MM/dd/yyyy”

change the format using
DateTime.ParseExact(yourdatevariable.toString(), “MM/dd/yyyy”, Globalization.CultureInfo.InvariantCulture)
.ToString(“dd/MM/yyyy”)

Thanks

3

It sounds like it’s being stored as a string in UiPath. If it’s being stored as a datetime, then using the .tostring(“dd/MM/yyyy”) would be working as expected.

Easiest way to fix it is converting to date with either CDate() or Datetime.parse or Datetime.parseexact

If it’s in dd/MM/yyyy i’d probably be lazy and just do CDate(excelDT.row(index).item(ColumnName)).ToString("dd/MM/yyyy")

1 Like
4

Thanks, I did this and it worked so long as the value in the Excel file is in the Date format and is right aligned. However, if it’s in the General format or if the date value isn’t right aligned it doesn’t seem to work as I get an error. For the error message was 'Conversion from string “30/11/2001” to type ‘Date is not valid.’ Any idea how to solve this kind of issue?

5

Sounds like the lazy method of CDate() isn’t going to work. Are you always going to get it in the same format? If so, you should use ParseExact.

Therefore it’d be DateTime.ParseExact(excelDT.row(index).item(ColumnName).ToString.Trim,"MM/dd/yyyy",CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

This will throw an error if it is not exactly “MM/dd/yyyy” format, but will remove any leading or trailing whitespace due to the .Trim

If it is sometimes coming in as a date and sometimes coming in as string then you’ll want to add additional input verification in as well. You can use the IsDate() function in an if statement to verify. So in an assign activity you could do something like this:
I will just call the row.item object you’re working with as ExcelDate for brevity.
Assign YourDateString = If(IsDate(ExcelDate),ExcelDate.ToString("dd/MM/yyyy"),DateTime.ParseExact(ExelDate.ToString.Trim,"dd/MM/yyyy",CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"))

6

I have since found a possible solution. It works in my case but not in all cases. What I’ve done is check the length of the value in question, it’s 10 characters or less (i.e. it’s only dd/MM/yyyy) then that means it can be directly converted to a string but if it’s longer then it’s in the DateTime format and I use the CDate as mentioned.