# Flagging multi-week activities across calendar weeks?

#### dyoung

##### New Member
can anybody help me come up with a 'streamlined' formula to resolve this one.........

The data I am trying to analyse is effectively a large number of work activities (45k+) due to occur over the next four year period. Amongst other details each activity has a start date and duration.

In a separate table I have columns split into 52 weeks per year. What I am trying to achieve is to insert a simple 'flag' ie a number 1 against each week that an activity occurs. From this I can use the flag as a multiplier in many further calculations.

For work activities starting and finishing in the same week a simple IF or LOOKUP function can be used. How though do I get a work activity that spans say 3 weeks to input a flag into the three respective week columns of my separate table. I have managed to resolve this with lengthy arithmetical calculations however this causes memory issues with the volume of formula entries that I require.

Any bright ideas greatfully received.

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome!

This could be one way assuming start dates are in chronological order.
Book1
ABCDEFG
1StartdateDurationdaysStartweekEndweekWeekActivity
22004-01-0151211
32004-01-1032321
42004-02-15107931
52004-03-0112101140
Sheet2

Thank you for the very quick response.....unfortunately, your solution is not exactly what I was after. Perhaps I can explain this in further detail as follows:

Each row now contains a separate work activity with a start week, an end week and a required resource. This resouce is required for each and every week that the work activity is ongoing. Ultimately I am trying to calculate the total resource required, per week, for all of the work activities.

I have so far managed to achieve this by creating a table of 52 columns (1 for each week) and using the following formula to insert a '1' in the relevant column whenever a work activity is in progress during that week:

=(IF(Activity_Start_Week<=Week_Num,1,0)*(IF(Activity_End_Week>=Week_Num,1,0)))

and thereafter using 'sumif' function to total the required resources for each activity having a '1' in the relevant week. Although this works okay it is VERY SLOW since the IF formula is present in 52 weeks * 45,000 activitys = long time!

There must be an easier way to do this but for the life of me I cannot think of it.

Any further ideas?

ps - hopefully this is clearer, I cannot download the html maker to display an example since I am using a work PC.

Is this more like you want?
Book1
ABCDEFGHIJK
1ActStartwEndwResourceweek12345
2aaa15wwResource
3bbb17xxww13322
4ccc23wwxx11221
5ddd24yyyy01110
6eee34zzzz00110
7fff25ww
8ggg34xx
Sheet3

Very nearly, only 'minor' problem is that each activity has a different quantity of resource ie. activity aaa may require 2 'ww' per week whereas activity ccc requires 1 'ww' per week and activity fff 3 'ww' per week.

I am thinking this should work quite simply with the addition of another array within the sumproduct calculation........or perhaps not?

Now formula looks like:

=SUMPRODUCT((\$D\$2:\$D\$10=\$F3)*(\$B\$2:\$B\$10< =G\$1)*(\$C\$2:\$C\$10 > =G\$1),\$E\$2:\$E\$10)
Book1
ABCDEFGHIJK
1ActStartwEndwResourcequantityweek12345
2aaa15ww2Resource
3bbb17xx5ww27766
4ccc23ww1xx55665
5ddd24yy2yy02220
6eee34zz3zz00330
7fff25ww4
8ggg34xx1
Sheet3

Fairwinds.......thank you.

I see that you even got your response time down to 10 mins last time round!

You are truly a Guru.

Yours humbled,
D

Replies
13
Views
1K
Replies
0
Views
273
Replies
7
Views
692
Replies
1
Views
361
Replies
1
Views
1K

1,217,755
Messages
6,138,422
Members
450,136
Latest member
Tabako1960

### 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?

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