Skip to main content

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.

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 😉 

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"

)

 

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.

 

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.

 


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"

)

 

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"

)

 


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

"

 

Reply


Terms & Conditions