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.
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.