If Expression

Question

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.

Community Question.xlsx

flying008 · Answer

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.

DararithYat · Answer

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 · Answer

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