How to identify if the Week of an End Date is in the PAST, ACTUAL, NEXT or FUTURE weeks?

[NLOIA]

Hi !

     I created a column to identify if the "End Date" of a task belongs to one of the weeks below, based on the ISO numbers for each week of the year (1~52 or 53). 
     But I am facing an error due to the year, that I cannot overpass. 
     It is considering the pure number of a week in 2023 (e.g.: #49) as a week in future, because the actual week number in 2024 is less than that.
     Please, can someone help me with it?

PAST: "End Date" week number < actual week number
THIS WEEK: "End Date" week number = actual week number
NEXT WEEK: "End Date" week number = actual week number +1
FUTURE: End "Date" week number = actual week number +2

Here is the actual formula I have that is running, depite the error from year:

"

IF(
{End Date},
IF(
AND(
YEAR({End Date}) = YEAR(TODAY()),
DATETIME_DIFF({End Date}, TODAY(), 'days') < 0
),
"PAST",
IF(
AND(
YEAR({End Date}) = YEAR(TODAY()),
VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(TODAY(), 'W'))
),
"THIS WEEK",
IF(
AND(
YEAR({End Date}) = YEAR(TODAY()),
VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(DATEADD(TODAY(), 7, 'days'), 'W'))
),
"NEXT WEEK",
"FUTURE WEEKS"
)
)
),
"No End Date"
)

"

I also tried this other formula, but it produces this error:  "Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."

"
IF(

  {End Date},

  IF(

    YEAR({End Date}) < YEAR(TODAY()),

    "PAST",

    IF(

      YEAR({End Date}) = YEAR(TODAY())

      AND VALUE(DATETIME_FORMAT({End Date}, 'W')) < VALUE(DATETIME_FORMAT(TODAY(), 'W')),

      "PAST",

      IF(

        YEAR({End Date}) = YEAR(TODAY())

        AND VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(TODAY(), 'W'))

        AND {End Date} >= TODAY(),

        "THIS WEEK",

        IF(

          YEAR({End Date}) = YEAR(TODAY())

          AND VALUE(DATETIME_FORMAT({End Date}, 'W')) = VALUE(DATETIME_FORMAT(DATEADD(TODAY(), 7, 'days'), 'W'))

          AND {End Date} >= TODAY() + INTERVAL '1 WEEK',

          "NEXT WEEK",

          "FUTURE WEEKS"

        )

      )

    )

  ),

  "No End Date"

)

"

Print of the result:


Thank you all in advance.

[Dimitris_Goudis]

Hey @NLOIA I would recommend to use datediff formula in weeks. This could minimize your current long formulas and also can help on your base performance 😉 

[TheTimeSavingCo]

Here's something I threw together which I think does what you want:

IF(
  AND(
    YEAR(Date) = YEAR(TODAY()),
    WEEKNUM(Date) = WEEKNUM(TODAY())
  ),
  "This week"
)
& 
IF(
  AND(
    YEAR(Date) = YEAR(TODAY()),
    WEEKNUM(Date) < WEEKNUM(TODAY())
  ),
  "Past"
)
& 
IF(
  AND(
    YEAR(Date) = YEAR(TODAY()),
    WEEKNUM(Date) > WEEKNUM(TODAY())
  ),
  IF(
    WEEKNUM(Date) = WEEKNUM(TODAY()) + 1,
    "Next week",
    "Future"
  )
)
&
IF(
  YEAR(Date) < YEAR(TODAY()),
  "Past"
)
&
IF(
  YEAR(Date) > YEAR(TODAY()),
  "Future"
)

[NLOIA]

Hi!

Thank you @TheTimeSavingCo for the proposal. But, unfortunatelly, it did not runs (print).

Below I paste the actual formula that is running almost well, since in the actual week it consider yesterday as past, despite belong to the actual week.

actual formula in use:

"

IF(

{End date},

IF(

VALUE(DATETIME_FORMAT({End date}, 'W')) < VALUE(DATETIME_FORMAT(TODAY(), 'W')),

"PAST",

IF(

VALUE(DATETIME_FORMAT({End date}, 'W')) = VALUE(DATETIME_FORMAT(TODAY(), 'W')),

IF(

{End date} >= TODAY(),

"THIS-WEEK",

"NEXT-WEEKS"

),

IF(

VALUE(DATETIME_FORMAT({End date}, 'W')) = VALUE(DATETIME_FORMAT(DATEADD(TODAY(), 7, "days"), 'W')),

"NEXT WEEK",

"FUTURE WEEKS"

)

)

),

"No End Date"

)

"

Print: the error of the proposed formula.

[TheTimeSavingCo · Accepted solution]

Ahh, you're going to need to update the formula so that it uses the "End Date" field.  Right now it's expecting to use a field called 'Date', which doesn't exist in your table

Here's a copy where that's been done:

IF(
  AND(
    YEAR({End date}) = YEAR(TODAY()),
    WEEKNUM({End date}) = WEEKNUM(TODAY())
  ),
  "This week"
)
& 
IF(
  AND(
    YEAR({End date}) = YEAR(TODAY()),
    WEEKNUM({End date}) < WEEKNUM(TODAY())
  ),
  "Past"
)
& 
IF(
  AND(
    YEAR({End date}) = YEAR(TODAY()),
    WEEKNUM({End date}) > WEEKNUM(TODAY())
  ),
  IF(
    WEEKNUM({End date}) = WEEKNUM(TODAY()) + 1,
    "Next week",
    "Future"
  )
)
&
IF(
  YEAR({End date}) < YEAR(TODAY()),
  "Past"
)
&
IF(
  YEAR({End date}) > YEAR(TODAY()),
  "Future"
)

[NLOIA]

Hi !

     Many thanks @TheTimeSavingCo !!
     Your formula did the job!  I will accept it as the solution.
     This allows better view of the tasks under the actual sprint and also the effort involved.

     I made just minor adjustments for a better description and statistics (print).
     The formula I adjusted is below:

"

IF(

  AND(

    YEAR({End date}) = YEAR(TODAY()),

    WEEKNUM({End date}) = WEEKNUM(TODAY())

  ),

  "2-THIS WEEK"

)

& 

IF(

  AND(

    YEAR({End date}) = YEAR(TODAY()),

    WEEKNUM({End date}) < WEEKNUM(TODAY())

  ),

  "1-PAST"

)

& 

IF(

  AND(

    YEAR({End date}) = YEAR(TODAY()),

    WEEKNUM({End date}) > WEEKNUM(TODAY())

  ),

  IF(

    WEEKNUM({End date}) = WEEKNUM(TODAY()) + 1,

    "3-NEXT WEEK",

    "4-FUTURE"

  )

)

&

IF(

  YEAR({End date}) < YEAR(TODAY()),

  "1-PAST"

)

&

IF(

  YEAR({End date}) > YEAR(TODAY()),

  "4-FUTURE"

)

"