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.

Find more posts tagged with

Accepted answers

Nick Korna

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.

All comments

Nick Korna

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.

Jon Barto

@Nick Korna That was perfect! Exactly what I was looking for Thanks so much!

Nick Korna

No problem, happy to have helped. 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.