"Fiscal Year" field creation with dates and text in same column

[Linders]

Hello,

I'm working on creating a fiscal year field from the column below that contains dates and text (data type V_WString). I'm running into an issue when I use the below formula and am getting [Null]. Any suggestions on how I achieve a new "fiscal year" with the desired outcome below?

Formula I used returning [Null]

IF [Column 1] >= "02/01/2023" AND [Due to EY (SOX Input)] <= "01/31/2024" THEN "FY 2024"
ELSEIF [Due to EY (SOX Input)] >= "02/01/2024" AND [Due to EY (SOX Input)] <= "01/31/2025" THEN "FY 2025"
ELSE Null()
ENDIF

Column 1	Desired Outcome "Fiscal Year"
WT	null
12/04/2023	FY 2024
03/15/2023	FY 2024
WT	null
02/15/2024	FY 2025
11/26/2023	FY 2024
11/26/2023	FY 2024
False	null
02/19/2024	FY 2025
WT	FY 2024

Thank you in advance for your help!

[Qiu · Accepted solution]

@Linders 
We need to convert your Date to dateTime format in order for comparing.

[Linders]

Hi @Qiu, thank you for your help! I figured I had to somehow split it out, but was stuck on the execution. Fantastic use of REGEX and learned something new, appreciate it!

[Qiu]

glad to help. 😁