Skip to main content

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

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

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


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

 

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

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

Reply


Terms & Conditions