Help

Re: Using formula to auto populate a next check date field based of single selection and last check

188 0
cancel
Showing results for 
Search instead for 
Did you mean: 
HarryBrown
4 - Data Explorer
4 - Data Explorer

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"

                      )

                  )

                )

              )

            )

        )

    )

)

)

1 Reply 1

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