Lesson#77: Scope the breakup program schedule and explain the formula

Here I have got a case where my colleague was working with a project schedule. He had a schedule with a start date, end date, and scope and he had to break up the scope month-wise. With a long schedule, this was very hectic work to break manually so I developed a formula. In this post, I will discuss what logic did I use to build this formula.

For that, I have developed two types of formulas, one for the first cells of the rows and the other for the next cells of the rows.

So for the first cells, the formula is like that if its start date month and year matches the first cell month and year then the result will be
$C2*((EOMONTH($A2,0)-$A2+1)/($B2-$A2+1)) else it will show “” (blank)
which means (remaining days of the start date month*scope/difference between the start date and end date) 
Another case can be there if the end date month lies on the month of the first cell
then it will simply show the whole scope
the complete formula will be like

=IF(AND(MONTH($B2)=MONTH(D$1),YEAR($B2)=YEAR(D$1)),$C2,

IF(AND(MONTH($A2)=MONTH(D$1),YEAR($A2)=YEAR(D$1)),$C2*((EOMONTH($A2,0)-$A2+1)/($B2-$A2+1)),””))

Now for the rest of the cells in the row
If the previous cell = “” then

if the month and year of start date match the cell month and year

$C2*(EOMONTH($A2,0)-$A2+1)/($B2-$A2+1)
If both the start date and end date come in the same month then it is just $C2


If the previous cell does not equal to “” that means it is having some value then
If the cell month and year matched the end date month and year the result will be $C2-SUM($D2:D2)

And if the cell year and month don’t match the end date year and month then $C2*(30/($B2-$A2+1))


or else it will be “” (blank)

added another if which doesn’t let the total value of scope breakup more than the scope means IF(SUM($D2:D2)>=$C2,””,

So the total formula is like
=IF(SUM($D2:D2)>=$C2,””,IF(D2=””,IF(AND(MONTH($A2)=MONTH(E$1),MONTH($A2)

=MONTH($B2),YEAR($A2)=YEAR(E$1),YEAR($A2)=YEAR($B2)),$C2,IF(AND(MONTH($A2)

=MONTH(E$1),YEAR($A2)=YEAR(E$1)),$C2*(EOMONTH($A2,0)-$A2+1)/($B2-$A2+1),””)),IF(AND(MONTH($B2)=MONTH(E$1),YEAR($B2)

=YEAR(E$1)),$C2-SUM($D2:D2),IF(OR(NOT(MONTH($B2)=MONTH(E$1)),NOT(YEAR($B2)

=YEAR(E$1))),$C2*(30/($B2-$A2+1)),””))))

If I am making you more confused then please download the sheet from the below link

Click Here to download