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

Topic Labels: Dates & Timezones Formulas
Solved
698 5
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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:

1 Solution

Accepted Solutions
18 - Pluto

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

5 Replies 5
9 - Sun

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 😉

18 - Pluto

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

6 - Interface Innovator

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.

18 - Pluto

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

6 - Interface Innovator

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

"