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

Lisa Matthews2

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!

Bassam Khalil

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

Bassam Khalil

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

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

@Lisa Matthews2

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