Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Formulas
558 1
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