WEEKNUMBER formula

holli.nunn

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?

Toufong Vang

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!

Toufong Vang

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

✅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!

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