Date Ranges

Question

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 · Accepted Answer

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

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 · Answer

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

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

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded