If Expression

[DararithYat]

I want to create a formula under "Grant Category" as highlight in Yellow for Q2-2021 period, but each category is different and I want to combine as follow conditions:

1. Any Grant Name that contain the word "CEO" within Q2-21 Grant Date => I want to show it as "Q2'21 CEO Performance Award"

2. Any Grant Name that contain the word "Performance Conversion" within Q2-21 Grant Date => I want to show it as "Q2'21 HK Performance Modification Award"

3. Any Grant Name that contain the word "Executive" within Q2-21 Grant Date => I want to show it as "Q2'21 Exec Awards"

4.The rest of Grant Name beside 1 2 3 => I want to show it as "Q2'21 Standard Awards"

- Do we have one formula to create all these conditions? 

- Could you please help to share workflow if possible? 

Appreciate it. 

[flying008]

Hi, @DararithYat 

Please see the below formula:

IF Contains([Grant Name], "CEO") THEN "Q2'21 CEO Performance Award" 
ELSEIF Contains([Grant Name], "Performance Conversion") THEN "Q2'21 HK Performance Modification Award" 
ELSEIF Contains([Grant Name], "Executive") THEN "Q2'21 Exec Awards" 
ELSE "Q2'21 Standard Awards" ENDIF

[DararithYat]

Thanks, but i want to put Grant Date condition one more: like if Grant date within in Q2-2021 and Grant Name contain.... how to merger these 2 condition together

IF[Grant Date] > ("2021-03-31") AND [Grant Date] < ("2021-07-01") THEN ...... IFcontain ([grant name], "CEO" then "Q2'21 CEO performance awards" and so on like you provided 

[flying008]

Hi, @DararithYat 

Try this formula again:

"Q" + tostring(ceil(DateTimeMonth(DateTimeParse([Grant Date],"%Y-%m-%d"))/3)) + "'" + tostring(DateTimeFormat(DateTimeParse([Grant Date],"%Y-%m-%d"),"%y")) + " " +
(IF Contains([Grant Name], "CEO") THEN "CEO Performance Award" 
ELSEIF Contains([Grant Name], "Performance Conversion") THEN "HK Performance Modification Award" 
ELSEIF Contains([Grant Name], "Executive") THEN "Exec Awards" 
ELSE "Standard Awards" ENDIF)

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.