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

    Adding of multiple dates with If function

    Arihant Jain

      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"

       

      Please help me getting the correct formula.

       

      Thanks

      Arihant Jain

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

          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
            sudheer.kumar.5

            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
              Sohan Jawar

              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.