XL Problem - Distribute No of Days in Months - Subtract 2 dates

Rahim.Zulfiqar.Ali · Sep 28, 2011

XL Problem - Distribute No of Days in respective months Months - Subtract 2 dates and follow current month

Unable to pos attachments, Please provide email address so that I can send you the file.

PROBLEM:
There are 2 columns namely START_DATE and END_DATE, If we subtract both dates we will have Total NO of Days.
For the 1st Customer its start date is 10-MAY-2012 and Ending date is 04-JULY-2012 which has total of 55 days in 3 months
Now for these 3 months I have to distribute the days MONTH WISE in the months cells given above highlighted in GREEN
For may it gives me 21 days for JUNE it gives me 30 and for JULY remaining 04 days and in rest of the months if fills FALSE

BUT the scenerio for next customer is change he has now more months days to distribute
So I require a formula / function or macro to solve this issue

You can add the columns or rows for getting the solution

Comfy · Sep 28, 2011

still needs work.

Comfy · Sep 28, 2011

Excel 2007<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;">January</td><td style="text-align: right;;">February</td><td style="text-align: right;;">March</td><td style="text-align: right;;">April</td><td style="text-align: right;;">May</td><td style="text-align: right;;">June</td><td style="text-align: right;;">July</td><td style="text-align: right;;">August</td><td style="text-align: right;;">September</td><td style="text-align: right;;">October</td><td style="text-align: right;;">November</td><td style="text-align: right;;">December</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Jul-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">24</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Aug-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Sep-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Oct-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">24</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">10-Jun-11</td><td style="text-align: right;;">04-Nov-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">10-Jul-11</td><td style="text-align: right;;">04-Dec-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">24</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">10-Aug-11</td><td style="text-align: right;;">04-Jan-12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">25</td></tr></tbody></table>Sheet1 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(MONTH(A$2)>MONTH(C$1),0,IF($B2-$A2 >DAY(DATE(YEAR(C$1),MONTH(C$1)+1,0)),DAY(DATE(YEAR(C$1),MONTH(C$1)+1,0))-DAY(A2),$B2-$A2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(D$1),0,IF(($B2-$A2)-SUM($C2:C2) >DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)),DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)),($B2-$A2)-SUM($C2:C2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(E$1),0,IF(($B2-$A2)-SUM($C2:D2) >DAY(DATE(YEAR(E$1),MONTH(E$1)+1,0)),DAY(DATE(YEAR(E$1),MONTH(E$1)+1,0)),($B2-$A2)-SUM($C2:D2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(F$1),0,IF(($B2-$A2)-SUM($C2:E2) >DAY(DATE(YEAR(F$1),MONTH(F$1)+1,0)),DAY(DATE(YEAR(F$1),MONTH(F$1)+1,0)),($B2-$A2)-SUM($C2:E2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(G$1),0,IF(($B2-$A2)-SUM($C2:F2) >DAY(DATE(YEAR(G$1),MONTH(G$1)+1,0)),DAY(DATE(YEAR(G$1),MONTH(G$1)+1,0)),($B2-$A2)-SUM($C2:F2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(H$1),0,IF(($B2-$A2)-SUM($C2:G2) >DAY(DATE(YEAR(H$1),MONTH(H$1)+1,0)),DAY(DATE(YEAR(H$1),MONTH(H$1)+1,0)),($B2-$A2)-SUM($C2:G2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(I$1),0,IF(($B2-$A2)-SUM($C2:H2) >DAY(DATE(YEAR(I$1),MONTH(I$1)+1,0)),DAY(DATE(YEAR(I$1),MONTH(I$1)+1,0)),($B2-$A2)-SUM($C2:H2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(J$1),0,IF(($B2-$A2)-SUM($C2:I2) >DAY(DATE(YEAR(J$1),MONTH(J$1)+1,0)),DAY(DATE(YEAR(J$1),MONTH(J$1)+1,0)),($B2-$A2)-SUM($C2:I2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(K$1),0,IF(($B2-$A2)-SUM($C2:J2) >DAY(DATE(YEAR(K$1),MONTH(K$1)+1,0)),DAY(DATE(YEAR(K$1),MONTH(K$1)+1,0)),($B2-$A2)-SUM($C2:J2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(L$1),0,IF(($B2-$A2)-SUM($C2:K2) >DAY(DATE(YEAR(L$1),MONTH(L$1)+1,0)),DAY(DATE(YEAR(L$1),MONTH(L$1)+1,0)),($B2-$A2)-SUM($C2:K2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(M$1),0,IF(($B2-$A2)-SUM($C2:L2) >DAY(DATE(YEAR(M$1),MONTH(M$1)+1,0)),DAY(DATE(YEAR(M$1),MONTH(M$1)+1,0)),($B2-$A2)-SUM($C2:L2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(N$1),0,IF(($B2-$A2)-SUM($C2:M2) >DAY(DATE(YEAR(N$1),MONTH(N$1)+1,0)),DAY(DATE(YEAR(N$1),MONTH(N$1)+1,0)),($B2-$A2)-SUM($C2:M2)))</td></tr></tbody></table></td></tr></table> 

Header Row from C1 to N1 contains dates (C1 = 01/01/2011, D1 = 01/02/2011) formatted using a custom date format mmmm

almost there. One more post and I think I'll have it

Comfy · Sep 28, 2011

Right I think I got it.
I just hope that this is what you asked for.

Excel 2007<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style="text-align: right;;">January</td><td style="text-align: right;;">February</td><td style="text-align: right;;">March</td><td style="text-align: right;;">April</td><td style="text-align: right;;">May</td><td style="text-align: right;;">June</td><td style="text-align: right;;">July</td><td style="text-align: right;;">August</td><td style="text-align: right;;">September</td><td style="text-align: right;;">October</td><td style="text-align: right;;">November</td><td style="text-align: right;;">December</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Jul-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Aug-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Sep-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">10-May-11</td><td style="text-align: right;;">04-Oct-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">10-Jun-11</td><td style="text-align: right;;">04-Nov-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">20</td><td style="text-align: right;;">31</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">10-Jul-11</td><td style="text-align: right;;">04-Dec-11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">10-Aug-11</td><td style="text-align: right;;">04-Jan-12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td><td style="text-align: right;;">30</td><td style="text-align: right;;">31</td></tr></tbody></table>Sheet1 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(MONTH(A$2)>MONTH(C$1),0,IF($B2-$A2 >DAY(DATE(YEAR(C$1),MONTH(C$1)+1,0)),DAY(DATE(YEAR(C$1),MONTH(C$1)+1,0))-DAY(A2),$B2-$A2))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(D$1),0,IF(($B2-$A2)-SUM($C2:C2) >DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)),IF(MONTH($A2)=MONTH(D$1),DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0))-DAY($A2),DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0))),($B2-$A2)-SUM($C2:C2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(E$1),0,IF(($B2-$A2)-SUM($C2:D2) >DAY(DATE(YEAR(E$1),MONTH(E$1)+1,0)),IF(MONTH($A2)=MONTH(E$1),DAY(DATE(YEAR(E$1),MONTH(E$1)+1,0))-DAY($A2),DAY(DATE(YEAR(E$1),MONTH(E$1)+1,0))),($B2-$A2)-SUM($C2:D2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(F$1),0,IF(($B2-$A2)-SUM($C2:E2) >DAY(DATE(YEAR(F$1),MONTH(F$1)+1,0)),IF(MONTH($A2)=MONTH(F$1),DAY(DATE(YEAR(F$1),MONTH(F$1)+1,0))-DAY($A2),DAY(DATE(YEAR(F$1),MONTH(F$1)+1,0))),($B2-$A2)-SUM($C2:E2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(G$1),0,IF(($B2-$A2)-SUM($C2:F2) >DAY(DATE(YEAR(G$1),MONTH(G$1)+1,0)),IF(MONTH($A2)=MONTH(G$1),DAY(DATE(YEAR(G$1),MONTH(G$1)+1,0))-DAY($A2),DAY(DATE(YEAR(G$1),MONTH(G$1)+1,0))),($B2-$A2)-SUM($C2:F2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(H$1),0,IF(($B2-$A2)-SUM($C2:G2) >DAY(DATE(YEAR(H$1),MONTH(H$1)+1,0)),IF(MONTH($A2)=MONTH(H$1),DAY(DATE(YEAR(H$1),MONTH(H$1)+1,0))-DAY($A2),DAY(DATE(YEAR(H$1),MONTH(H$1)+1,0))),($B2-$A2)-SUM($C2:G2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(I$1),0,IF(($B2-$A2)-SUM($C2:H2) >DAY(DATE(YEAR(I$1),MONTH(I$1)+1,0)),IF(MONTH($A2)=MONTH(I$1),DAY(DATE(YEAR(I$1),MONTH(I$1)+1,0))-DAY($A2),DAY(DATE(YEAR(I$1),MONTH(I$1)+1,0))),($B2-$A2)-SUM($C2:H2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(J$1),0,IF(($B2-$A2)-SUM($C2:I2) >DAY(DATE(YEAR(J$1),MONTH(J$1)+1,0)),IF(MONTH($A2)=MONTH(J$1),DAY(DATE(YEAR(J$1),MONTH(J$1)+1,0))-DAY($A2),DAY(DATE(YEAR(J$1),MONTH(J$1)+1,0))),($B2-$A2)-SUM($C2:I2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(K$1),0,IF(($B2-$A2)-SUM($C2:J2) >DAY(DATE(YEAR(K$1),MONTH(K$1)+1,0)),IF(MONTH($A2)=MONTH(K$1),DAY(DATE(YEAR(K$1),MONTH(K$1)+1,0))-DAY($A2),DAY(DATE(YEAR(K$1),MONTH(K$1)+1,0))),($B2-$A2)-SUM($C2:J2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(L$1),0,IF(($B2-$A2)-SUM($C2:K2) >DAY(DATE(YEAR(L$1),MONTH(L$1)+1,0)),IF(MONTH($A2)=MONTH(L$1),DAY(DATE(YEAR(L$1),MONTH(L$1)+1,0))-DAY($A2),DAY(DATE(YEAR(L$1),MONTH(L$1)+1,0))),($B2-$A2)-SUM($C2:K2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(M$1),0,IF(($B2-$A2)-SUM($C2:L2) >DAY(DATE(YEAR(M$1),MONTH(M$1)+1,0)),IF(MONTH($A2)=MONTH(M$1),DAY(DATE(YEAR(M$1),MONTH(M$1)+1,0))-DAY($A2),DAY(DATE(YEAR(M$1),MONTH(M$1)+1,0))),($B2-$A2)-SUM($C2:L2)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=IF(MONTH($A2)>MONTH(N$1),0,IF(($B2-$A2)-SUM($C2:M2) >DAY(DATE(YEAR(N$1),MONTH(N$1)+1,0)),IF(MONTH($A2)=MONTH(N$1),DAY(DATE(YEAR(N$1),MONTH(N$1)+1,0))-DAY($A2),DAY(DATE(YEAR(N$1),MONTH(N$1)+1,0))),($B2-$A2)-SUM($C2:M2)))</td></tr></tbody></table></td></tr></table>

barry houdini · Sep 28, 2011

You could get that result with this formula in C2 copied across and down

=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2+1)+1)

Note that if start on 10th May through to beyond the end of May then the logical consequence of that is that if you start on 31st May the number of days in May is zero, is that right?

Comfy · Sep 28, 2011

barry houdini said:
=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2+1)+1)

Oh so simple

XL Problem - Distribute No of Days in Months - Subtract 2 dates

Rahim.Zulfiqar.Ali

New Member

Excel Facts

Comfy

Well-known Member

Comfy

Well-known Member

Comfy

Well-known Member

barry houdini

MrExcel MVP

Comfy

Well-known Member

Similar threads

Forum statistics

Share this page

XL Problem - Distribute No of Days in Months - Subtract 2 dates

Rahim.Zulfiqar.Ali

New Member

Excel Facts

Comfy

Well-known Member

Comfy

Well-known Member

Comfy

Well-known Member

barry houdini

MrExcel MVP

Comfy

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock