Hello,
i’ve this problem: i read a column of type “date” in excel file…
In excel file date format is dd/MM/yyyy (12/30/2021)
when i read the value from uipath the format date is MM/dd/yyyy, but i need the date in format ddMMyyyy…
How can i achieve this? Because i’ve done this:
CDATE(datarow(“document data”).tostring).tostring(“ddMMyyyy”)
but in the production environment this fails, maybe because it has different date format
Hi @dolihi2116 ,
Welcome to UiPath Forum 
DateTime.ParseExtract("12/30/2021","MM/dd/yyyy",System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat).ToString("ddMMyyyy")
Regards,
Arivu
ParseExact not ParseExtract
thanks, but consider that the extracted date from excel is inside my transaction item.
If i put transactionItem(“Document date”).tostring instead of date “30/12/2021” uipath give an error like “string was not recognized as a valid datetime”
i have regional settings with “dd/MM/yyyy”, excel column in date format “dd/MM/yyyy”, when i read excel range the item is in “MM/dd/yyyy” format. what settings does it take?
The second argument of ParseExact is the format of the string you’re giving it as the first argument.
So if 30/12/2021 is what’s in Document Date, then you have to change the input format…
DateTime.ParseExact(transactionItem.SpecificContent(“Document date”).ToString,“dd/MM/yyyy”,System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat).ToString(“ddMMyyyy”)
You can try using DateTime.Parse instead of ParseExact if you have multiple different formats it might encounter. Parse tries to “figure it out” whereas with ParseExact the string has to match the format you tell it.
Thank you very much for the help.
But i’m a little bit confused about the resolution.
In first vdi with parse exact i’ve error “string was not recognized as a valid date time” because it read MM/dd/yyyy. In second vdi, with same excel file, the result date is in format dd/MM/yyyy, same as excel file, so this works…
What i want to know, is: why sometimes the format read from the same excel is in different format? Thx
Because that’s how the data was written to the Excel file. Open the Excel file manually and right-click one of the date cells. What’s the format set to? Check a few of them and see what the formats are set to in Excel.
in excel is dd/MM/yyyy but in uipath the date format is MM/dd/yyyy, that’s my problem.
I’ve checked preserve format in read range
Conversion Functions like CDATE, Convert.DateTime … will work out of box with some formats but will not work with dd/MM//yyyy format
In that case we take higher control with DateTime.ParsExact(YourString, YourFormat, System.Globalization.CultureInfo.InvariantCulture)
this method has also some more method signatures. Also we can pass some local culture details to it … etc.
Once we haf parsed within a datetime we can reformat it as by our needs with the toString Method of a dateTime variable.
Excel values can be different after a read range within the datatable. Here also EAS and workbook read range can have different results. So we are only on the safe side, when checking what is in the datatable for a value and a format.
When dates are parseable with a different format definition then often it is the case, that it is not recognized.
Example: CDate(“12/02/2020”) will work for a dd/MM/yyyy and a MM/dd/yyyy but the result is a 2nd dec 2020 and will be wrong when dd/MM/yyyy is expected. Simialr /same when we also work with parseExact
Thanks @ppr for your explanation,
can I ask you a question? Why on two different excel, with the same column format(date-dd/MM/yyyy) when I read it from uipath in the first case it reads me the date in excel format(dd/MM/yyyy) and in second case uipath reads the datetime in MM/dd/yyyy hh:mm:ss format?
Thanks in advance
there are maybe more speculations as proven facts. One explanation is that the cell type will trigger the result. In RnDs came also to cases where it was not 100% predictible or weird.
You can stabilize with following:
or offering multiple formats within the DateTime.ParseExact method
However please don’t get my words in a way that it will lead to panic. In the majority, we do succeed with using the essential steps. But it helps us for doing reliable implementations when we remember to all these facts by selecting a solution strategy for our cases
Anil_G
sallavudeen_ashwin
Sachin_Jadhav