Trying to populate monthly data based on a start and end date of a project.

Question

Hi All,
Am trying to populate monthly data, for a number of projects which have different start &amp; end dates.&nbsp;&nbsp;
&nbsp;The monthly data the revenue expected for each month. This is calculated by looking up a % revenue expected per month ( from another table) X total revenue expected for the project in the 4th column of the table - as per below:&nbsp;
&nbsp;

&nbsp;Start&nbsp;&nbsp;
&nbsp;DurMonths&nbsp;
&nbsp;Finish&nbsp;
&nbsp;Revenue&nbsp;
Jan-17
Feb-17
Mar-17
Apr-17
May-17

01/03/2017
12
28/02/2018
100
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

01/01/2016
24
31/12/2017
80
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

01/04/2016
36
31/03/2019
70
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

01/05/2017
40
31/08/2020
60
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

&nbsp;
Example of the total % revenue expected table :&nbsp;
&nbsp;

Month
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12

%
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.15
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.15
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.15
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.25
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.25
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.25
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.35
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.45
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.35
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.25
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.24
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.23

&nbsp;
I have tried to use a vlookup &amp; match function to do this but struggling. Just wondering i this is the right apporach or if there is another clever function / approach that I could take. Any help much appreciated. Thank You. Neale

damien_rosario · Answer

Hi Neale Forth
&nbsp;
I hope I have understood right. Please see the attached file to see if what I have interpreted is correct?
&nbsp;
Cheers
Damien

neale forth · Answer

Hi Damien.
&nbsp;
Many thanks for your reply.
Apologies but I have not explained the problem very well !
&nbsp;
Your solution populates the months for each project however its not synchronised with the project start &amp; end dates.&nbsp; So in my example , first row/ project, starts in March 17. So for the first project, Jan 17 and Feb 17 columns would be zero however March 17 would look up the 0.15%....... does that make sense ?
&nbsp;
Many thanks for your time/effort,
&nbsp;
Neale
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

damien_rosario · Answer

Hi NealeI get it now. I will need to think about it as I don't know of a solution off the top of my head. Hopefully some of the other skilled community members will have some ideas!Will let you know if I come up with anything.CheersDamien

Forum Discussion

Trying to populate monthly data based on a start and end date of a project.

3 Replies

Resources

Start	DurMonths	Finish	Revenue	Jan-17	Feb-17	Mar-17	Apr-17	May-17
01/03/2017	12	28/02/2018	100
01/01/2016	24	31/12/2017	80
01/04/2016	36	31/03/2019	70
01/05/2017	40	31/08/2020	60

Month	1	2	3	4	5	6	7	8	9	10	11	12
%	0.15	0.15	0.15	0.25	0.25	0.25	0.35	0.45	0.35	0.25	0.24	0.23