Date Ranges

[KGB]

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!!!!

[TheTimeSavingCo]

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]

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

 

[TheTimeSavingCo · Accepted solution]

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"
    )
  )
)

[KGB]

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