Counting weeks in a cycle
I need help with a formula to count weeks in a cycle.
The cycle start date was 5/1/23. End cycle will be 4/30/24 for example.
So I need to display the current week in the cycle as of today. I don't use the today function because it does not update unless the sheet is opened.
So instead of the today function I have a helper column called today date and have an automation put a date (which would be today's date in the "today date" column every day at 1am)
So how do I set up a formula to do that? So this week would be week 36.
Best Answer
-
Hi @Jon Barto,
If you have a cycle start and current date, would something like this work:
=ROUNDUP(([Current Date]@row + 1 - [Cycle Start Date]@row) / 7)
This will calculate the difference between the 2 dates and divide by 7 to give the week number. The +1 is so the start date is counted as week 1, rather than 0.
Hope this helps, but if there are any problems or you've any queries, let us know.
Answers
-
Hi @Jon Barto,
If you have a cycle start and current date, would something like this work:
=ROUNDUP(([Current Date]@row + 1 - [Cycle Start Date]@row) / 7)
This will calculate the difference between the 2 dates and divide by 7 to give the week number. The +1 is so the start date is counted as week 1, rather than 0.
Hope this helps, but if there are any problems or you've any queries, let us know.
-
@Nick Korna That was perfect! Exactly what I was looking for Thanks so much!
-
No problem, happy to have helped. 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 211 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!