1 Reply Latest reply on Aug 30, 2018 12:40 PM by Okechukwu Ossai

    IF statement for Date

    Jeffrey Elie

      I have a dataset with due dates and I am trying to show the status of a list of projects using the date these projects are due. I want to create a Red, Amber, Green status for these projects using a calculated field looking at the Due Date field.

       

      I create a calculated field like this but it's not working...

       

      IF ATTR([KPI Performance])="Screening on Time" and ATTR([Milestone Due Date])<=TODAY() then "Red"

      ELSEIF  ATTR([KPI Performance])="Screening on Time" and (ATTR([Milestone Due Date])>TODAY()+ 2) AND ATTR([Milestone Due Date])<TODAY() +12) then "Amber"

      ELSEIF  ATTR([KPI Performance])="Screening on Time" and ATTR([Milestone Due Date])>=TODAY() +12 then "Green"

      END

       

      for the secondline, I want to basically say, if the date field is 2 days ahead but less than 12 days ahead, "Amber"

      for the third line, I want to say if the due date is 12 days or more in the future then "Green"

      Using TODAY() +12 doesn't seem to work

       

      Any help would be greatly appreciated.

        • 1. Re: IF statement for Date
          Okechukwu Ossai

          Replace TODAY()+2 with DATEADD('day', 2, TODAY())

           

          Also replace TODAY()+12 with DATEADD('day', 12, TODAY())

           

          Something like this.

           

          IF ATTR([KPI Performance]) = "Screening on Time" AND ATTR([Milestone Due Date]) <= TODAY() THEN "Red"

          ELSEIF ATTR([KPI Performance]) = "Screening on Time" AND ATTR([Milestone Due Date]) >= DATEADD('day', 2, TODAY())

          AND ATTR([Milestone Due Date]) < DATEADD('day', 12, TODAY()) THEN "Amber"

          ELSEIF  ATTR([KPI Performance]) = "Screening on Time" AND ATTR([Milestone Due Date]) >= DATEADD('day', 12, TODAY()) THEN "Green"

          END

           

          You can also rewrite the formula as;

          IF ATTR([KPI Performance])= "Screening on Time" THEN

              IF ATTR([Milestone Due Date]) <= TODAY() THEN "Red"

              ELSEIF ATTR([Milestone Due Date]) >= DATEADD('day', 2, TODAY())

              AND ATTR([Milestone Due Date]) < DATEADD('day', 12, TODAY()) THEN "Amber"

              ELSEIF ATTR([KPI Performance]) = "Screening on Time"

              AND ATTR([Milestone Due Date]) >= DATEADD('day', 12, TODAY()) THEN "Green"

              END

          END

           

          Just looking at the formula, I notice that dates from Today+1 to Today+2 were excluded.

          For example, projects with Milestone Due Date as tomorrow and Sept 1st will return as null. I changed the equality sign around Today+2. So, the code will only exclude dates that are Today+1. Is this intentional?

           

           

          Hope this helps.

          Ossai

          1 of 1 people found this helpful