Project Week Number by project start/end date

Meg Y

I am needing to calculate actual project week number as majority of projects cross multiple years.

The current WeekNumber formula goes by calendar year, therefore resets each January.

Project start - 12/04/2023 / Project end - 03/10/2025

12/04/2023 = week 1

03/10/2025 = week 56

Paul Newcome

In that case you would use something along the lines of

=INT(([Date Column]@row - MIN(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], @cell <> ""))) / 7) + 1

Paul Newcome

Where would this week number be populated exactly? Are you able to provide screenshots for context?

Meg Y

@Paul Newcome It would be populated in the project plan. Right now it is just for one report.

Goal is to show project week number alongside task start date, assigned to, estimated effort, and actual capacity for external clients.

Paul Newcome

On every row?

Are you concerned with the week numbers following specific days on the calendar (Monday to Sunday type of thing) or would the start of the week be based on the start of the project so that some would be Tuesday through Monday and others could be Friday through Thursday based on the day the project actually starts?

Meg Y

@Paul Newcome It would be based on the project start date and not a specific day of week.

Paul Newcome

In that case you would use something along the lines of

=INT(([Date Column]@row - MIN(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], @cell <> ""))) / 7) + 1