Current Week and Next Week Formula

Cesar Perez · 04/16/21

Hi, I need help with a formula that will help me determine if a date is the current week or the next week in the calendar. Need the weeks to star on Sundays. I'm using the following two formulas:

For Current Week I'm using the following formula, which is working fine:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(ChangeDate@row + 1)), "CurrentWeek")

For Next Week I'm using the following formula:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY() + 1) + 1 = WEEKNUMBER(ChangeDate@row)), "NextWeek")

The problem is that the Next Week formula is pushing the days by 1. For example: 4/18/21 is the Sunday starting week 16. However, the formula is skipping this day and picking 4/19/21 as the first day of the week.

Cesar Perez · 05/12/21

I read this post https://community.smartsheet.com/discussion/65416/weeknumber-and-weekday and solved my question. Here are the formulas in case anyone can use them.

Last Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 2 = WEEKNUMBER([Change Date]@row)), "Last Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER([Change Date]@row)), "Last Week")))

Current Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row + 1)), "Current Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row)), "Current Week")))

Next Week Formula

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

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) + 1 = WEEKNUMBER([Change Date]@row)), "Next Week")))

Andrée Starå · 04/17/21

Hi @Cesar Perez

I hope you're well and safe!

Try something like this.

=IF(WEEKNUMBER(ChangeDate@row) = WEEKNUMBER(TODAY()); "CurrentWeek"; IF(WEEKNUMBER(ChangeDate@row) = WEEKNUMBER(TODAY()) + 1; "NextWeek"; ""))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Rajul Khandelwal · 04/17/21

Hi Cesar,

If I understood your function correctly then you are mapping current week and next week on the basis of Year(Today()) and WeekNumber(Today). I am not quite getting the use of +1 in your formula. Your formula is currently checking the week number of Today() with the the next day rather than that day itself. I have updated the formula below. Hope it works.

For Current Week I'm using the following formula, which is working fine:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(ChangeDate@row ~~+ 1~~)), "CurrentWeek")

For Next Week I'm using the following formula:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY() ~~+ 1~~) + 1 = WEEKNUMBER(ChangeDate@row)), "NextWeek")

Regards,

Rajul Khandelwal

Cesar Perez · 04/19/21

Thank you both for your prompt response. The problem I have is that the WEEKNUMBER function starts the weeks on Mondays. This means that the first day of this week is Monday, 4/19/21. I need it to calculate starting on Sundays or 4/18/21. Our work week follows regular calendar Sunday through Saturday.

Cesar Perez · 05/12/21