Current Week and Next Week Formula

Options
✭✭✭✭

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.

Best Answer

• ✭✭✭✭
Answer ✓
Options

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

Answers

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭
Answer ✓
Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!