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")))
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.

| improve this answer | |
  • Thanks for your help! – T Zhang Sep 16 '19 at 13:58
  • Welcome. Please upvote(^) and accept it as an answer. – Ganesh Sanap Sep 16 '19 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")))
| improve this answer | |
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")))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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