formula to determine week #s included in range of dates (start/finish)

Hello,

I am trying to figure out a formula which will return the week numbers for a range of dates... for example, a task with start date of July 15 and a finish/due date of July 30 would fall across week #s 28, 29, 30.

The goal is to run a filter to display work for a particular week (or multiple weeks). I'm having trouble with how to create this and there may be a different (and better) solution which may accomplish the same thing... any help is welcome and needed! Thank you, in advance, for taking a look at this and helping me!

Below is an image of what I would like to do... I just can't figure out how to calculate and display the multiple weeks!


Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Lisa Matthews2 

    Hope you are fine, as i understand from your question you need a formula to display in Week # column all the week numbers between start and finish date the you use this column to filter any activity in selected week, if this what you need please inform me if you have activity start for example in 1-May-2021 and finish in 30-Dec-2021 did you know how many week number you will have in that row.

    i think it's better to use a different approach to define in selected week which tasks are running. so i have this solution for you:

    1- create 2 column Week # Start and Week # Finish.

    2- use the following formula to calculate the week number:

    Week # Start = WEEKNUMBER([Planned Start]@row)

    Week # Finish = WEEKNUMBER([Planned Finish]@row)

    and convert those formula to column format formula.

    then you can use the following filter to check any compensation of start and finish week number 

    the result


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Lisa Matthews2 

    Hope you are fine, as i understand from your question you need a formula to display in Week # column all the week numbers between start and finish date the you use this column to filter any activity in selected week, if this what you need please inform me if you have activity start for example in 1-May-2021 and finish in 30-Dec-2021 did you know how many week number you will have in that row.

    i think it's better to use a different approach to define in selected week which tasks are running. so i have this solution for you:

    1- create 2 column Week # Start and Week # Finish.

    2- use the following formula to calculate the week number:

    Week # Start = WEEKNUMBER([Planned Start]@row)

    Week # Finish = WEEKNUMBER([Planned Finish]@row)

    and convert those formula to column format formula.

    then you can use the following filter to check any compensation of start and finish week number 

    the result


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Lisa Matthews2
    Lisa Matthews2 ✭✭✭✭

    Hello Bassam,

    Your recommendation solves my problem! This is exactly what I need! Thank you so much for taking time to help me!

    --Lisa

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Lisa Matthews2

    Excellent, I will be happy to help you any time.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!