Require a formula to indicate how many days fall within a series of months

if A1 is start Date and A2 is end date

PHP Code: 

=A2-A1-NETWORKDAYS(A1,A2,2) 


Either:

In your first cell of choice:

=NETWORKDAYS(MAX($A$1,EOMONTH($A$1,ROWS($1:1)-2)+1),MIN($A$2,EOMONTH($A$1,ROWS($1:1)-1)))

Copy down as required.

Or, put May, June, July in C1, C2 and C3, and enter this in D1:

=SUMPRODUCT(0+(TEXT(ROW(INDIRECT($A$1&":"&$A$2)),"mmmm")=C1),0+(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),11)<6))

Again, copy down as required.

Regards

Edit: I should've mentioned that both these solutions require your months to be in the same year.

Let me know if that is potentially not the case.

Regards

Hi John, it will be easier if you can format your month name to Jan-14, Feb-14 like this for easier calculation

Try this version:

=IF(ROWS($1:1)>DATEDIF($A$1,$A$2,"m")+2,"",NETWORKDAYS(MAX($A$1,EOMONTH($A$1,ROWS($1:1)-2)+1),MIN($A$2,EOMONTH($A$1,ROWS($1:1)-1))))

Copy down as required.

You can enter your months/years next to these if you want, though the formula doesn't depend on it.

Regards

Are your dates in the cells as indicated?

Edit: the formula should be:

=IF(ROWS($1:1)>DATEDIF($A$1,$A$2,"m")+2-(DAY($A$2)>=DAY($A$1)),"",NETWORKDAYS(MAX($A$1,EOMONTH($A$1,ROWS($1:1)-2)+1),MIN($A$2,EOMONTH($A$1,ROWS($1:1)-1))))

Regards

You haven't even attempted to adapt it. The formula was looking at A1 and A2. Your dates are in B1 and B2.

The formula in C6 should be:

=IF(COLUMNS($A:A)>DATEDIF($B$1,$B$2,"m")+2-(DAY($B$2)>=DAY($B$1)),"",NETWORKDAYS(MAX($B$1,EOMONTH($B$1,COLUMNS($A:A)-2)+1),MIN($B$2,EOMONTH($B$1,COLUMNS($A:A)-1))))

Note that this formula does not depend on the entries above it in rows 4 and 5. Rather, it takes the month/year of the entry in B1, and gives the required days for that month and each following month up to and including the date in B2.

Regards

You're welcome!