WEEKNUMBER formula
Hello, I am trying to count the number of weeks between two dates. I have applied this formula
=WEEKNUMBER([SD End]@row - WEEKNUMBER([SD Start]@row))
It doesn't seem to be calculating correctly. For example, returning "51" for a SD End date 2/3/23 - SD Start 11/14/22. That answer should be more like 11, right? What am I missing?
Best Answers
-
Hi, @holli.nunn, modify your formula to...
=(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)
You needed to account for start-end dates that span across a calendar year so the expression,
YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 ,
gives you the year(s) measured in weeks. -
Hi @holli.nunn
I hope you're well and safe!
=(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)
Try copying/pasting the formula.
Did that work?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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.
Answers
-
Hi, @holli.nunn, modify your formula to...
=(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)
You needed to account for start-end dates that span across a calendar year so the expression,
YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 ,
gives you the year(s) measured in weeks. -
Ah. thank you for the insight! I've applied this formula and it now returns as #invalid operation, can you see what I'm doing wrong just by the formula?
=(YEAR([SD End]@row - YEAR([SD Start]@row * 52 + WEEKNUMBER([SD End]@row - WEEKNUMBER([SD Start]@row)))))
-
Hi @holli.nunn
I hope you're well and safe!
=(YEAR([SD End]@row) - YEAR([SD Start]@row)) * 52 + WEEKNUMBER([SD End]@row) - WEEKNUMBER([SD Start]@row)
Try copying/pasting the formula.
Did that work?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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.
-
Phew! Perfect. Thank you much for the help!
-
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 tried your method, and it worked across the year, except the week ending December still shows a negative number
-
Never mind… i fixed it with a simple roundup sum formula (yes it is supposed to return the higher number as well) Thanks!
=ROUNDUP(SUM([End Date]@row - [Start Date]@row) / 7, 0)
first column is the old formula:
=(YEAR([End Date]@row) - YEAR([Start Date]@row)) * 52 + WEEKNUMBER([End Date]@row) - WEEKNUMBER([Start Date]@row)
-
Why not just take the total number of days and divide by 7?
=([End Date]@row - [Start Date]@row) / 7
You could incorporate a ROUND / ROUNDUP / ROUNDDOWN function if needed, but that should pretty much cover everything regardless of months, years, etc..
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!