WEEK # in Month Calculation
Hi there,
I'm using this formula to calculate what week number in a month a week is. For example 1/1/24 would be week 1 and 1/7 would be week 2, 1/14 starts week three etc. I'm getting 1/14 coming through as week 2. Any suggestions or ideas on how I could clean up this formula to function properly?
=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1
Best Answer
-
All we need is an IF to say that if the weekday is a Sunday (1) then add one more to the final calculation.
=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1, 0)
Answers
-
Hi @Dakota Haeffner,
Internally, Smartsheet uses Monday as the first day of the week, that is why you are getting these results.
Hope that helps,
Dave
-
Help to know thank you! Seems like a strange way to do it.
Hoping someone might be able to give me idea for a solution oriented formula.
-
Hi,
I understand that you are trying to calculate the week number in a month based on a given date. It seems like there might be an issue with the formula you provided.
Here's an updated formula that should help you achieve the desired result:
=WEEKNUM(Date@row, 2) - WEEKNUM(DATE(YEAR(Date@row), MONTH(Date@row), 1), 2) + 1
Please give this formula a try and let me know if it works correctly for you. If not, please provide some sample dates and their expected week numbers so that I can further assist you.
Best regards,
Ryan Kramer
Have you backed up your solution lately? https://www.smartsheetbackups.com/
-
I hope you're well and safe!
To add to Ryan's excellent advice/answer.
It seems like Ryan was in Excel mode.
Here's the Smartsheet version.
=WEEKNUMBER(Date@row, 2) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1), 2) + 1
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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.
-
I appreciate the collaboration and effort guys!
Unfortunately, this still returned week 2 for 1/14/24. 😫
=WEEKNUMBER([Event Date]@row, 2) - WEEKNUMBER(DATE(YEAR([Event Date]@row), MONTH([Event Date]@row), 1), 2) + 1
-
Sample dates look like it would be any Sunday this year.
1/14/24 should return Week 3, but it shows Week 2,
1/21/24 should return Week 4, but it shows Week 3,
1/28/24 should return Wekk 5, but it shows Week 4, etc..
-
All we need is an IF to say that if the weekday is a Sunday (1) then add one more to the final calculation.
=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1, 0)
-
@Paul Newcome GOAT thank you Paul!
-
Happy to help. 👍️
-
Hi @Paul Newcome,
Can you explain the significance of this portion of the equation - "=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1".
Doesn't that provide the same value as "=WEEKNUMBER(Date@Row)"?
If the formula is shortened to "=WEEKNUMBER(Date@row) + IF(WEEKDAY(Date@row) = 1, 1, 0)" it seems to give the desired outcome (utilizing your IF suggestion).
Any insight as to why the longer formula is recommended/preferred would be appreciated.
-
@DKazatsky2 The formula is intended to provide the week number within the month. Your formula will provide the week number within the year.
-
Ahhhh, I completely missed that distinction. Thanks very much for the reply.
-
Nice.
Thanks for this.
Continuous Improvement Facilitator in HVAC industry || Timezone CES
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!