Date issue with, result are not correct

[Dav]

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!

[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. 

[OTrieger]

[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

[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