UiPath not reading Excel dates in consistent formats

Help
, , ,
1

Hello! I am automating a process where I need to extract a date (“M/d/yyyy”) format from Excel. However when I access this date in UiPath, I noticed that sometimes it’s in “M/d/yyyy” format and sometimes it’s in the “M/d/yyy 00:00:00” format. I didn’t realize it was inconsistent…

I want the date in the “M/dd/yyyy” format. When it has the time stamp I do this:
DateTime.ParseExact(Excel_InvoiceDate, “M/d/yyyy 00:00:00”,
System.Globalization.CultureInfo.InvariantCulture).ToString(“M/dd/yyyy”)

What should I do to take into account both the time stamp case and the no timestamp case?

2

Hi @Hisuhong

give the format like this “M/dd/yyyy hh:mm:ss”

DateTime.ParseExact(Excel_InvoiceDate, “M/dd/yyyy hh:mm:ss”,
System.Globalization.CultureInfo.InvariantCulture).ToString(“M/dd/yyyy”)

Thanks
ashwin S

3

The issue is that if the date doesn’t have the time stamp, it returns an error. And some dates in Excel have this time stamp when I read it in Excel while some don’t, even though they are formatted in the same way in Excel.

4

Oh never mind. Let me try your suggestion

5

Hello @Hisuhong

The reason for this is, in the excel sheet, we can convert the data format as we needed irrespective of the exact format that is behind in the cell values. However, when reading the excel file through UiPath, it doesn’t consider the format of the values that we see in the excel file. it extracts the exact value as it is. Hence, if the values are in different formats, this will happen.

So what you can do is, get the values converted to the needed format just like @AshwinS2 mentioned…

6

@Lahiru.Fernando @AshwinS2 I think I know what my issue is. When I read the Excel data, if I check “PreserveFormat” it will not give me the time stamp, but it will if I un check it. :sweat_smile:

7

Hi
the expression would be like this which will take if the value has timestamp or if not as well

DateTime.ParseExact(Excel_InvoiceDate.ToString.SubString(0,9).Trim, “M/d/yyyy”,
System.Globalization.CultureInfo.InvariantCulture).ToString(“M/dd/yyyy”)

Cheers @Hisuhong

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.