1

I thought this is easy but somehow I didn't get this right.

I entered the following formula in the calculated column but the outcome is incorrect.

For example, when [Target_Finish]=10/10/2019, the column should return "FY20", but it returns "FY19".

=if([Target_Finish]="","",if([Target_Finish]<"9/30/2019","FY19",if(and([Target_Finish]>"9/30/2019",[Target_Finish]<"9/30/2020"),"FY20","FY21")))

3 Answers 3

1

You need to convert the date string you are using in comparison to Date object for correct comparison.

For that you need to use DATE function.

Syntax: DATE(year,month,day)

Try using below formula:

=IF([Target_Finish]="","",IF([Target_Finish]<DATE(2019,9,30),"FY19",IF(AND([Target_Finish]>DATE(2019,9,30),[Target_Finish]<DATE(2020,9,30)),"FY20","FY21")))

Reference:

DATE function.

2
  • Thanks for your help!
    – T Zhang
    Sep 16, 2019 at 13:58
  • Welcome. Please upvote(^) and accept it as an answer. Sep 16, 2019 at 13:58
0

While comparing, you have compared a Date column with a string "9/30/2019". To convert it into date value, you should use DATEVALUE() function inside the formula. Use this formula below.

=IF(ISBLANK([Target_Finish]),"",IF([Target_Finish]<DATEVALUE("9/30/2019"),"FY19",IF(AND([Target_Finish]>DATEVALUE("9/30/2019"),[Target_Finish]<DATEVALUE("9/30/2020")),"FY20","FY21")))
0

You have entered date in a wrong format I think, Please try to compare date in following format.

=IF([Target_Finish]="","",IF([Target_Finish]<DATE(2019,9,30),"FY19",IF(AND([Target_Finish]>DATE(2019,9,30),[Target_Finish]<DATE(2020,9,30)),"FY20","FY21")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.