Previous, Current, and Next Weeks with New Year

Cesar Perez

Hi All,

I'm wondering if there is a more efficient way to identify the previous week, current week, and future weeks when the year changes to the new year. I am using the following formula:

=IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY(30)), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = -49), "Tree Weeks", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY(30)), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = -50), "Two Weeks", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY(30)), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = -51), "Next Week", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY(7)), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = 51), "Previous Week", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY()), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = -1), "Previous Week", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY()), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = 0), "Current Week", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY()), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = 1), "Next Week", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY()), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = 2), "Two Weeks", 

IF(AND(YEAR([Change Start Date]@row) = YEAR(TODAY()), WEEKNUMBER([Change Start Date]@row) - WEEKNUMBER(TODAY()) = 3), "Tree Weeks")))))))))

Here is the sheet setup:

Toufong Vang

Another approach is to use NETDAYS() to find the number of days between the two dates, and then divide it by 7. Taking the integer, INT(), will return the number of weeks. The expression is:

INT(NETDAYS(TODAY(), [Change Start Date]@row)/7)

Use IF() to evaluate when the Change Start Date is in the "Past X Week(s)", "Current Week", or "X Week(s)".

When the number is negative, multiple it by (-1) to avoid "Past -3 Week(s)".

IF(weekexpression = 0, "Current Week", IF( weekexpression < 0, "Past " + ((-1) * weekexpression) + " Week(s)", weekexpression + " Week(s)"))

Toufong Vang

You are correct, @Cesar Perez , the expression is counting the days instead of the actual week number of the year. To do that, we'll need to use the WEEKNUMBER() function.

The expression for the approach using WEEKNUMBER() would be..

WEEKNUMBER([Change Start Date]@row) + ((YEAR([Change Start Date]@row) - YEAR(TODAY())) * 52) - WEEKNUMBER(TODAY())

((YEAR([Change Start Date]@row) - YEAR(TODAY())) * 52) ensures that the appropriate number of weeks (52 per year) is added or subtracted for the difference between the two dates' years.

Below is a cleaner structure of the IF() expression to evaluate for when the Change Start Date is a past, current, or future date.

IF(weekexpression = 0, "Current Week", IF( weekexpression < 0, "Past " + ABS(weekexpression) + " Week(s)", weekexpression + " Week(s)"))

Toufong Vang

Another approach is to use NETDAYS() to find the number of days between the two dates, and then divide it by 7. Taking the integer, INT(), will return the number of weeks. The expression is:

INT(NETDAYS(TODAY(), [Change Start Date]@row)/7)

Use IF() to evaluate when the Change Start Date is in the "Past X Week(s)", "Current Week", or "X Week(s)".

When the number is negative, multiple it by (-1) to avoid "Past -3 Week(s)".

IF(weekexpression = 0, "Current Week", IF( weekexpression < 0, "Past " + ((-1) * weekexpression) + " Week(s)", weekexpression + " Week(s)"))

Lucas Rayala

@Cesar Perez -- I don't have time to flesh this out, but, assuming the date you are referencing is in a column called Date, could you do something like:

YEAR(Date@row)<>YEAR(Date@row+7)

Use this method in an IF statement to determine if the years are different when you add a week should provide the data point you need?

Cesar Perez

Thank you @Lucas Rayala for the quick response

thank you @Toufong Vang for the quick response. I tried your solution and it works. However, I am getting an #UNPARSABLE error when I tried the formula to adjust for past weeks. Can you review my formula? This is what I have:

=IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) = 0, "Current Week",

IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) < 0, "Past " +

((-1) * INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) + "3 Weeks",

INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) + " 2 Weeks"))

Toufong Vang

((-1) * INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7)) 

You're missing a closing parenthesis in the expression.

Happy to be of help!

Cesar Perez

Thank you @Toufong Vang, I missed it completely. One more question regarding the expression. It is not calculating properly. Why is 12/27/23 results to 0 instead of 1, or 1/2/24 and 1/12/24 results to 1 instead of 2?

Here is the formula:

=IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) = 0, "Current Week",

IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) = 1, "Next Week",

IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) = 2, "Two Weeks",

IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) = 3, "Three Weeks",

IF(INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) < 0, "Past " + ((-1) * INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7)) + " Weeks", INT(NETDAYS(TODAY(), [Change Start Date]@row) / 7) + " Weeks")))))

Toufong Vang

You are correct, @Cesar Perez , the expression is counting the days instead of the actual week number of the year. To do that, we'll need to use the WEEKNUMBER() function.

The expression for the approach using WEEKNUMBER() would be..

WEEKNUMBER([Change Start Date]@row) + ((YEAR([Change Start Date]@row) - YEAR(TODAY())) * 52) - WEEKNUMBER(TODAY())

((YEAR([Change Start Date]@row) - YEAR(TODAY())) * 52) ensures that the appropriate number of weeks (52 per year) is added or subtracted for the difference between the two dates' years.

Below is a cleaner structure of the IF() expression to evaluate for when the Change Start Date is a past, current, or future date.

IF(weekexpression = 0, "Current Week", IF( weekexpression < 0, "Past " + ABS(weekexpression) + " Week(s)", weekexpression + " Week(s)"))

Cesar Perez

Wow, you are a lifesaver @Toufong Vang. Thank you for the help