Current Week and Next Week Formula
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
-
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
-
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!
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.
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!