How to distribute Qty equally Among Months in case of change in dates.

[Pankaj Kumar]

Dear Expert team,

I am facing problem in preparing one excel template as per below requirement and attached sheet-

1. Project Start Date and Project Finish Date---- As soon as I change in the project start date and Project Finish Date under cell C2 and C3, Months need to be distributed accordingly from Cell F5, G5, H5 and so on to reflect as per project start month and Project Finish Month automatically.

2. Total Amount, as mentioned under Cell E6 to be equally distributed in the Months Under Cell as per months entered in start date and Finish date . For example- C6- 5th Jan26 and D6- 10-Jan-26-- So total Amount Qty under Cell E6 i.e 200 to be mentioned under F6. ( Total Jan`26 mentioned).

3. Similiarly, Dates under mentioned C7 and D7. Total diff of these dates is 33 Days and out of these 33 days , 28 days are under Jan and 5 days are under Feb. So total Qty of E7 i.e 400 to be distributed as per below-

Under Cell F7 i.e Under jan `26= (400*28)/33
Under Cell G7 i.e Under Feb `26= (400*5)/33

4. All the data under Cell F6 to T6 to be filled automatically as soon as we change the dates under Columns C6 & D6 onwards.

 

[shamimarita]

Hello Pankaj Kumar,

You can achieve this with formulas so that both the month headers and the distributed quantities update automatically whenever the dates change.

1. Generate the Month Headers

• In cell F5, enter the following formula:

=IF(EDATE(EOMONTH($C$2,-1)+1,COLUMNS($F:F)-1)<=EOMONTH($C$3,-1)+1,EDATE(EOMONTH($C$2,-1)+1,COLUMNS($F:F)-1),"")

• Then drag the formula to the right up to T5.
• Format these cells using a custom date format such as: mmm'yy

This will display the months as Jan'26, Feb'26, Mar'26, etc., based on the Project Start Date in C2 and Project Finish Date in C3.

2. Distribute the Quantity Based on Days in Each Month

• In cell F6, enter:

=IF(OR(F$5="",$C6="",$D6="",$E6=""),"",IFERROR($E6*MAX(0,MIN($D6,EOMONTH(F$5,0)+1)-MAX($C6,F$5))/($D6-$C6),0))

• Then drag the formula across from F to T and downward for the remaining rows.

The formula calculates how many days of the period in columns C fall within each month and distributes the quantity in column E proportionally.

For example, if the duration is 33 days, with 28 days in January and 5 days in February, and the total quantity is 400, the result will be:

January: 400 × 28 / 33 = 339.39
February: 400 × 5 / 33 = 60.61

If both dates fall within the same month, the entire quantity will automatically be assigned to that month.

This approach follows your example by calculating the total number of days as Finish Date − Start Date.