Formula to flag rolling weeks

rfigueroa1976

New Member
Joined
Jan 10, 2019
Messages
14
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"
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Last edited:
Upvote 0
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).
 
Upvote 0
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})
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top