Date issue with, result are not correct

Hi,

Could someone assist me with an issue regarding dates. I have data from excel is formatted in general format. The dates appear as 03.01.2025 in Excel.

I have written a formula to change the Excel data into date format using DateTimeParse([Transaction_Date], "%d.%m.%Y"), and there are no issues with that, I hope.

However, I have another formula where I want to add a column that indicates if the date is equal to or greater than a certain date, it should show "Current". If the date is equal to or less than a different date, it should show "Old". If the data is not there, it should show "Unknown". The formula I used is below, but the results are not correct. All of my cells are showing "Old" even though some dates are after the specified date. Please see the formula below:

IF DateTimeParse([Transaction_Date], "%Y-%m-%d") > DateTimeParse("2025-09-30", "%Y-%m-%d") THEN "Current" ELSEIF DateTimeParse([Transaction_Date], "%Y-%m-%d") <= DateTimeParse("2025-09-30", "%Y-%m-%d") THEN "OLD" ELSE "Unknown" // Optional: Handle cases that don't fit either condition ENDIF

Thank you for your help!

Alteryx Practice

Accepted answers

Dav

changed my formula but I still get the incorrect result. My column Rate current or old should be Current but it showing old, even tho the date is greater

Screenshot 2025-02-14 173101.png Screenshot 2025-02-14 172833.png

All comments

Dav

OTrieger

@Dav
Hello Dav,
It will not be enough to transform the format of the date, you actually need to have a date data type.

You should use the Date Parse tool to get the data in date type and then your formula will work, only you will not need to use the formatting in it.

Dav

I thought I have done that. Let me show you below what I have done below. In my first formula, I thought I was changing the data to date

Can you let me know, what extra I need to do.

Screenshot 2025-02-14 145700.png Screenshot 2025-02-14 145744.png Screenshot 2025-02-14 145837.png Screenshot 2025-02-14 145918.png

OTrieger

As you can see the formula is not changing the data type, actually now you can with a Select tool just change it into Date, no need to use the DateTime Parse tool as the format is already set, just need to ensure that the field has a date data type.

Dav

Hi, thank you again for helping. I am a complete novice, Im using AI and youtube to to build this automation. I tried using the select tool but my result in the cell come up empty. I even tried the convert tool, I left the data as string and that too come back null

The screen shots are me using the select tool

Screenshot 2025-02-14 153044.png Screenshot 2025-02-14 153021.png Screenshot 2025-02-14 153328.png

OTrieger

That it actually cool, as you managed to get it 95% right. So actually you did well. Keep going and do not forget to check data type, you always an do it with select tool.

Dav

Can you tell me how I can correct the date as as its still not working

OTrieger

Before that you change the data type in select tool, use Formula tool
DateTimeParse([Field], "%d.%m.%Y") so it will be in the correct format.
Do it for each of the date fields and then change them to Date

Dav

I believe I have followed your advice, formula first, then select tool, then a created a simple formula to say if the date if past this date, then "Current" if not than "old" but all my data come back as old

Screenshot 2025-02-14 165255.png Screenshot 2025-02-14 165310.png Screenshot 2025-02-14 165357.png Screenshot 2025-02-14 165625.png

OTrieger

Working fine for me

Dav

My issue is, when I ask the data to confirm if the data is prior or past the selected date, it bring back the incorrect answer. My last two pictures

OTrieger

This part is wrong

OTrieger

Try
If [........] >= "2025-09-30" THEN .... ENDIF

OTrieger

2025-09-30 is a static value, no need to parse it as it is in the correct format.

Dav

changed my formula but I still get the incorrect result. My column Rate current or old should be Current but it showing old, even tho the date is greater

Screenshot 2025-02-14 173101.png Screenshot 2025-02-14 172833.png

OTrieger

The date is not greater as all the values shows January, that is why you get Old. It compares September to January, same year 2025.

Dav

You are a life saver. Cant believe I had the date as 2025. Thank you so much

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335