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.

Date Ranges

Topic Labels: Formulas
Solved
Jump to Solution
1687 4
cancel
Showing results for 
Search instead for 
Did you mean: 
KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!

I am looking for a formula that will update on a month basis to pull data for a meeting.

I have a few dates I want to pull from - Target Start, Target Completion Date, and Dev start. I would like to label any records that have a date in the upcoming month (ex. March as our review is the last week in Feb) to be labeled as "Now." Then any content that has a date falling in the next 3-months to be labeled as "Near" and all remaining to be "Next". Any thoughts?? The date ranges for Now and Near could overlap, meaning it is being worked on over a longer period of time and should also reflect as Now.

Thank you!!!!

1 Solution

Accepted Solutions

Hmm, this got super complicated and I feel like there's a simpler way but can't figure it out.  In any case, try this out:

IF(
  OR(
    AND(
      Start,
      Onboard = "",
      IF(
        Start,
        IS_BEFORE(
          Start,
          TODAY()
        )
      )
    ),
    AND(
      Onboard,
      Start = "",
      IF(
        Onboard,
        IS_BEFORE(
          Onboard,
          TODAY()
        )
      )
    ),
    AND(
      Start,
      Onboard,
      IF(
        Start,
        IS_BEFORE(
          Start,
          TODAY()
        )
      ),
      IF(
        Onboard,
        IS_BEFORE(
          Onboard,
          TODAY()
        )
      )
    )
  ),
  "PAST",
  IF(
    OR(
      MONTH(Start) = MONTH(TODAY()),
      MONTH(Onboard) = MONTH(TODAY()),
      IS_BEFORE(
        Start,
        TODAY()
      ),
      IS_BEFORE(
        Onboard,
        TODAY()
      )
    ),
    "NOW",
    IF(
      OR(
        IF(
          Start,
          IS_BEFORE(
            Start,
            DATEADD(
              TODAY(),
              3,
              'months'
            )
          )
        ),
        IF(
          Onboard,
          IS_BEFORE(
            Onboard,
            DATEADD(
              TODAY(),
              3,
              'months'
            )
          )
        )
      ),
      "NEAR"
    )
  )
)

Screenshot 2024-02-18 at 1.26.04 PM.png

See Solution in Thread

4 Replies 4

Hm, could you provide a screenshot of example data and example output?  This formula gets tricky depending on how your data's formatted and what your intended result is

KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

Yes! I have these date ranges below - I would like it to if the start/on boarding is in the current month or past and to reflect as "NOW", those with a date for the processing or on-boarding within the next 3mo to reflect as "NEAR" ... after thinking about it, i dont need the completion date considered

Screen Shot 2024-02-16 at 11.03.08 AM.png

Screen Shot 2024-02-16 at 11.02.25 AM.png

 

Hmm, this got super complicated and I feel like there's a simpler way but can't figure it out.  In any case, try this out:

IF(
  OR(
    AND(
      Start,
      Onboard = "",
      IF(
        Start,
        IS_BEFORE(
          Start,
          TODAY()
        )
      )
    ),
    AND(
      Onboard,
      Start = "",
      IF(
        Onboard,
        IS_BEFORE(
          Onboard,
          TODAY()
        )
      )
    ),
    AND(
      Start,
      Onboard,
      IF(
        Start,
        IS_BEFORE(
          Start,
          TODAY()
        )
      ),
      IF(
        Onboard,
        IS_BEFORE(
          Onboard,
          TODAY()
        )
      )
    )
  ),
  "PAST",
  IF(
    OR(
      MONTH(Start) = MONTH(TODAY()),
      MONTH(Onboard) = MONTH(TODAY()),
      IS_BEFORE(
        Start,
        TODAY()
      ),
      IS_BEFORE(
        Onboard,
        TODAY()
      )
    ),
    "NOW",
    IF(
      OR(
        IF(
          Start,
          IS_BEFORE(
            Start,
            DATEADD(
              TODAY(),
              3,
              'months'
            )
          )
        ),
        IF(
          Onboard,
          IS_BEFORE(
            Onboard,
            DATEADD(
              TODAY(),
              3,
              'months'
            )
          )
        )
      ),
      "NEAR"
    )
  )
)

Screenshot 2024-02-18 at 1.26.04 PM.png

KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

Sorry! I was on vacation - THANK YOU SO MUCH!!