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