Formula to flag rolling weeks

[rfigueroa1976]

Is there a formula that would let me flag that last ten weeks from current date, taking into consideration that week 52 (sometimes 53) rolls back to week 1

For example.... Below is column A (The date) and B (the flag) I'd like the formula to look at the date in the cell and if the date in the cell is within 10 weeks from today's week number, flag as "Rolling 10", otherwise, leave blank... =if(weeknum(today()-weeknum(a1)<11. "Rolling 10", "") <-- obviously does not work. Your help will be greatly appreciated!



9/30/2018
10/7/2018
10/14/2018
10/21/2018
10/28/2018
11/4/2018 - "Rolling 10"
11/11/2018 - "Rolling 10"
11/18/2018 - "Rolling 10"
11/25/2018 - "Rolling 10"
12/2/2018 - "Rolling 10"
12/9/2018 - "Rolling 10"
12/16/2018 - "Rolling 10"
12/23/2018 - "Rolling 10"
12/30/2018 - "Rolling 10"
1/6/2019 - "Rolling 10"

 

[Tetra201]

Try

=IF(ROUNDUP(TODAY()/7,0)-ROUNDUP(A1/7,0)<11,"Rolling 10","")

 

[rfigueroa1976]

Thank you!

 

[rfigueroa1976]

Follow up question... i now need to have 10 cells display 10 weeks of "week of dates" but using a formula so that the dates dynamically change depending on today's date. (look at today's date, what is the week of date it belongs to, and display that date, then go back a week, then another week, etc until 10 cells are filled with "week of dates").

So today's date is 01/18/19, Cells a1 through a10 will show as listed below and continue to change as the weeks go on... Thanks for your help in advance.


11/4/2018
11/11/2018
11/18/2018
11/25/2018
12/2/2018
12/9/2018
12/16/2018
12/23/2018
12/30/2018
01/06/2019

 

[Tetra201]

Here is hot to get the 10 dates with a single formula:

1. Select range A1:A10 starting from cell A1.
2. In the formula bar, type or paste the following: =1+7*(ROUNDDOWN(TODAY()/7,0)-{10;9;8;7;6;5;4;3;2;1})
3. Press and hold Ctrl and Shift, then press Enter -- range A1:A10 will populate with the requested dates (may need to be formatted as Short Date).

 

[rfigueroa1976]

Nice! And it will automatically update (look at date when opened and change dates)?

 

[rfigueroa1976]

Also, how about if i wanted to start backward from 12/30/18, not 01/06/19?

 

[Tetra201]

Yes, the dates will automatically update.

If you want to have 10 dates starting backward from 12/30/18, just subtract an extra week:

=1+7*(ROUNDDOWN(TODAY()/7,0)-1-{10;9;8;7;6;5;4;3;2;1})

 

[rfigueroa1976]

Awesome! Thank you! Now... i'll ask this other question and you tell me if it's better to create a separate thread...

The formula that you provide is giving me the separated dates by week; however, when i create a chart on data generated from those dates, the chart automatically fills in the with rest of the dates in between the weekly dates... meaning instead of the axis on the chart displaying the data points of 12/23/18, 12/30/18, 01/06/18 as the columns to display.. it's displaying 12/23/18, 12/24/18, 12/25/18, etc... and the column (visual representation of the counts) is only on the dates intended.... Again, i'd show you a screenshot but, i don't have rights right now.f

 

[rfigueroa1976]

Disregard the last question about the chart. Found it... Right click the axis, select format axis, in the axis type section, change from "automatically detect based on data" to "text axis". Problem solved.