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
Check out the Formula Handbook template!