Skip to main content

Hello,

Struggling to work out where I'm going wrong. I'm trying to use a formula to auto update the "next check date" of equipment based on the date it was last checked and a single selection box.

Eg. Harness checked 1/1/24

Check frequency Monthly

Next check date 1/2/24

My current formula is as follows:

 

IF(

  {Check Frequency} = "Annualy",

  DATEADD(

    {Check date}, 1, "year"

    ),

    IF(

      {Check Frequency} = "6Monthly",

      DATEADD(

        {Check date}, 6, "months"

        ),

        IF(

          {Check Frequency} = "Quareterly",

          DATEADD(

            {Check date}, 3, "months"

            ),

            IF(

              {Check Frequency} = "BiMonthly",

              DATEADD(

                {Check date}, 2, "months"

              ),

              IF(

                {Check Frequency} = "Monthly",

                DATEADD(

                  {Check date}, 1, "month",

                ),

                IF(

                  {Check Frequency} = "BiWeekly",

                  DATEADD(

                    {Check date}, 2, "weeks"

                  ),

                  IF(

                    {Check Frequency} = "Weekly",

                    DATEADD(

                      {Check date}, 1, "week"

                      )

                  )

                )

              )

            )

        )

    )

)

)

Hmm, I think you had an extra comma in there.  Try this:

IF( {Check Frequency} = "Annualy", DATEADD( {Check Date}, 1, "year" ), IF( {Check Frequency} = "6Monthly", DATEADD( {Check Date}, 6, "months" ), IF( {Check Frequency} = "Quareterly", DATEADD( {Check Date}, 3, "months" ), IF( {Check Frequency} = "BiMonthly", DATEADD( {Check Date}, 2, "months" ), IF( {Check Frequency} = "Monthly", DATEADD( {Check Date}, 1, "month" ), IF( {Check Frequency} = "BiWeekly", DATEADD( {Check Date}, 2, "weeks" ), IF( {Check Frequency} = "Weekly", DATEADD( {Check Date}, 1, "week" ) ) ) ) ) ) ) )

You might also want to look into using a SWITCH instead