3 Replies Latest reply on Jun 13, 2018 11:11 PM by Sohan Jawar

Adding of multiple dates with If function

Hello,

I have created below formula but it is not giving me desired results. I want to show separate spend for December 2017 and then total of december' 17, Jan' 18, Feb'18 & Mar' 18 calling it as End of 2018 Q1 and then december' 17, Jan' 18, Feb'18, Mar' 18, April'18, May'18 & June'18 and so on. Below is the formula that I have created.

IF  MONTH([Creation Date])<=9 AND YEAR([Creation Date])=2017 THEN "End of 2017 Q3"

ELSEIF (MONTH([Creation Date])<=9 AND YEAR([Creation Date])=2017)  OR  (MONTH([Creation Date])=10 AND YEAR([Creation Date])=2017) OR

(MONTH([Creation Date])<=11 AND YEAR([Creation Date])=2017)  OR (MONTH([Creation Date])<=12 AND YEAR([Creation Date])=2017)

THEN "End of 2017 Q4"

ELSEIF   (MONTH([Creation Date])=12 AND YEAR([Creation Date])=2017) OR (MONTH([Creation Date])=1

AND YEAR([Creation Date])=2018) OR (MONTH([Creation Date])=2 AND YEAR([Creation Date])=2018) OR

(MONTH([Creation Date])=3 AND YEAR([Creation Date])=2018) THEN "End of 2018 Q1"

Thanks

Arihant Jain

• 1. Re: Adding of multiple dates with If function

Arihant,

One Suggestion, A Packaged Workbook always gets you faster help, plus Helps us to test your formula.

Thanks

Deepak

• 2. Re: Adding of multiple dates with If function

Hello Arihant,

Can you try with the below formula:

IF MONTH([Creation Date])<=9 AND YEAR([Creation Date])=2017

THEN "End of 2017 Q3"

ELSEIF

(MONTH([Creation Date])=10 AND YEAR([Creation Date])=2017) OR

(MONTH([Creation Date])<=11 AND YEAR([Creation Date])=2017) OR

(MONTH([Creation Date])<=12 AND YEAR([Creation Date])=2017)

THEN "End of 2017 Q4"

ELSEIF

(MONTH([Creation Date])=1 AND YEAR([Creation Date])=2018) OR

(MONTH([Creation Date])=2 AND YEAR([Creation Date])=2018) OR

(MONTH([Creation Date])=3 AND YEAR([Creation Date])=2018)

THEN "End of 2018 Q1"

END

S

• 3. Re: Adding of multiple dates with If function

Try the below:

IF (MONTH([Creation Date])<=3 AND YEAR([Creation Date]) = 2018) OR (MONTH([Creation Date] = 12) AND YEAR([Creation Date]) = 2017) THEN "End of 2018 Q1"

ELSEIF (MONTH([Creation Date])<=6 AND YEAR([Creation Date]) = 2018) OR (MONTH([Creation Date]) = 12 AND YEAR([Creation Date] = 2017) "End of 2018 Q2"

ELSEIF (MONTH([Creation Date])<=9 AND YEAR([Creation Date]) = 2018) OR (MONTH([Creation Date]) = 12 AND YEAR([Creation Date] = 2017) "End of 2018 Q3"

ELSE "End of 2018 Q4"

END

Note that IF condition will follow a preceding order. i.e if the first condition is true it will not move to the next and show the result of the first.

Regards

Sohan.