I need to do operation on dates based on formats
but when i have problem with these types of dates , uipath returns true for both the formats - i need only MM/dd/yyyy format
but in this case 09/06/2018 is in dd/MM/yyyy formats , still tryParse works for both
Thanks in advance
Do you want to convert your date format from dd/MM/yyyy to MM/dd/yyyy?
No i have some calculations based on date formats , and i need to find dates which are in dd/MM/yyyy format
but for the above sample both MM/dd/yyyy and dd/MM/yyyy gives yes
From which application you are trying to find out dates of particular format?
is it Excel Or Web application?
@kavya.s16 Yes, the bot might encounter issue in this case since it was not able to identify which is the month and date
Is it the same format that you are receiving as input?
@sb001 reading dates from excel
@ushu Problem is format is same it differs
for the above example the reason is 09 and 06 comes both in month and date
Only when the number goes beyond 12 bot will look that as date not month because we have only 12 months
If the date value is
12/24/2018 and if you have format as MM/dd/yyyy in try parse it will give true
If it is like 14/24/2018 then it will throw error as there is 14 month
If you want to do for this then as a part of standardisation the month has to be kept either with three characters like Feb or full like February
Only then you will get exact Boolean value
Hope this clarifies
@Palaniyappan Hi Sir thanks for the reply ,
Yeah i understood how it works , but the issue in my case is if date is of “dd/MM/yyyy” format it should throw exception
i am using tryparse and in dateformat defining “MM/dd/yyyy” and here this particular date is in “dd/MM/yyyy” still passing that parse condition
And dateformat is not constant in the excel, do u know any better for this issue
Hi @kavya.s16 ,
I do think for these kind of problems, we need to manage with the source data.
We need to Understand the different formats present in the Excel. This can be done by Inspecting using Debug or using a
Write Line Activity.
Once, it is confirmed there are different formats. We might be able to help you further.
Could you provide us a Screenshot of Different date formats? Just to confirm we are dealing with different types of date formats.
give a try on forcing the read range to handle the date as OADates. Then parse the date by
If the date formats are indeed different, as you found
We could try using VBA to change the
Date Column to a
Number Column Type, where the values of Date will appear in the
We could then Convert the
ODate format to a Fixed
DateTime format as needed.
Below is the VBA Code :
Below is the Workflow that show cases the Conversion :
Change Column Type.zip (19.3 KB)
To make the Column Detection in Excel Dynamic, I have used read range to Identify the Position of the Column Index and then convert the Index to Excel Column Letter.
But if the Position is known beforehand, we could use it directly by passing the value in
Invoke VBA. This can eliminate the use of
Read Range inside Excel Scope.
The Last Read Range is used just to view the Changed date formats and how to convert the values to a
DateTime and use it’s String format.
Let us know if this helps in your case.