Flagging multi-week activities across calendar weeks?


New Member
Jan 15, 2004
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.

This could be one way assuming start dates are in chronological order.
Upvote 0
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:


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.
Upvote 0
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?
Upvote 0
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)
Upvote 0
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,
Upvote 0

Forum statistics

Latest member

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