Project Week Number by project start/end date

Meg Y
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

Meg Young
mmyoungconsulting@gmail.com

If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/03/24 Answer ✓

    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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Meg Y
    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.

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Paul Newcome
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Meg Y
    Meg Y ✭✭✭✭✭✭

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

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/03/24 Answer ✓

    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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!