Compare dates- different formats

[simran_dua]

Hi all, 

Is there a way to compare the exact value of two column in the data imported in alteryx irrespective of the format. 

For example if I have two data files that I imported in alteryx one has a date in mm/dd/yyyy format and the other has dd-mm-yyyy format, but the date are the same, if I use the simple "if" function in alteryx it gives me that the dates are not the same. 

Thanks 

[drew9]

Hi @simran_dua , 

Currently you are reading in the dates as string data types. For you to do a true comparison of dates, you will need to make sure that dates you're comparing are all date data types.

You can accomplish this with the DateTime tool. 

[DavidP]

hi @simran_dua, if you convert both to the standard date format, you can compare them. This can be done in a single statement like:

if DateTimeParse([date1],'%m/%d/%Y') = DateTimeParse([date2],'%d-%m-%Y') then 'Yes' else 'No' endif

[simran_dua]

Hi thanks for this, I actually did use the datetime tool and the the two dates are in the date format, just the date format in itself are different. I was wondering if if we can compare irrespective of the formats. so if one date is January 1, 2021 and the other is 01/01/2021 it should give the out that they are the same. Similarly for numbers as well.

[simran_dua]

Thank you David,is there a way we can do it without changing the formats? because excel irrespective of the format can match the dates.

[DawnDuong · Accepted solution]

hi @simran_dua 

In Excel, what you see on the face of the spreadsheet is the "formatted data". If you click on the cell, then you can see the reall input. Thus, there are cases in Excel where 2 dates LOOK different but are the same, because the data in 2 cells are identical albeit different formatted.

So in Alteryx, when we read in Excel, it is read in as "text" based on whatever that is formatted in Excel, so we will need to bring back the as-presented to the "data-form" so to speak before values can be compared.

Hope this clarifies.

Dawn.