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?

Find more posts tagged with

Formulas

Accepted answers

TVang

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.

Andrée Starå

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!

All comments

TVang

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.

holli.nunn

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)))))

Andrée Starå

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

holli.nunn

Phew! Perfect. Thank you much for the help!

Andrée Starå

@holli.nunn

Excellent!

Happy to help!

I only assisted. Toufung gave the solution.

Deonizia Egan

I tried your method, and it worked across the year, except the week ending December still shows a negative number

Deonizia Egan

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)

Paul Newcome

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..

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.