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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!