Compare dates and identify date formats

Help Studio
, , ,
1

Hi Everyone,

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

09/06/2018
06/09/2018

Thanks in advance

2

Do you want to convert your date format from dd/MM/yyyy to MM/dd/yyyy?

3

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

4

From which application you are trying to find out dates of particular format?
is it Excel Or Web application?

5

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

6

@sb001 reading dates from excel

7

@ushu Problem is format is same it differs

8

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

Cheers @kavya.s16

9

@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

thanks

10

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.

11

give a try on forcing the read range to handle the date as OADates. Then parse the date by
grafik

12

Can u please elaborate

13

@kavya.s16 ,

If the date formats are indeed different, as you found "dd/MM/yyyy" and "MM/dd/yyyy".

We could try using VBA to change the Date Column to a Number Column Type, where the values of Date will appear in the ODate Format.

We could then Convert the ODate format to a Fixed DateTime format as needed.

Below is the VBA Code :

Sub ChangeFormat(sheetName,ExcelColumn)
    Sheets(sheetName).Activate
    Range(ExcelColumn+":"+ExcelColumn).NumberFormat="0.00"
End Sub

Below is the Workflow that show cases the Conversion :
image
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.