Current Week Formula Question
Hi all! I have a current week formula on a sheet =IF(Week@row = WEEKNUMBER(TODAY()), 1)
For the week we are currently in, it is counting Monday as the beginning of the week and I need the week to start on Sunday. Is there a way to adjust this with my forumla?
Answers
-
You'll need to account for the day being a Sunday by using the WEEKDAY function...
=IF(WEEKDAY(TODAY()) = 1, IF(Week@row = WEEKNUMBER(TODAY(+1)), 1, IF(Week@row = WEEKNUMBER(TODAY()), 1)))
(Check the color-coding on the parentheses to make sure I have them correct. )
The logic is: IF the WEEKDAY value for today is 1 (a Sunday,) then run this IF formula: IF(Week@row = the week number for tomorrow's date, 1). If the WEEKDAY value for today is not a 1 (any other day,) then run this formula: IF(Week@row = the week number for today's date, 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff! I tried that forumla and it took all the information away from all cells 🤨
Here's what I tried instead, but it still doesn't count the 19th as the current week
-
@tmichelle068 Seeing your data structure helps!
What you really want to alter is the function in the Week column, so that dates that fall on Sunday reflect the week number for the following day:
=IF(WEEKDAY([Date:]@row) = 1, WEEKNUMBER([Date:]@row + 1), WEEKNUMBER([Date:]@row))
If the date in the Date: cell is a Sunday, get the week number for the next day, otherwise, just get the week number for that date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
That worked! Thank you SO much!!
-
Excellent! Happy to help!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!