Counting the number of each month between two dates?

[CollinRosser]

Howdy,

I am attempting to build a calculator that quantifies weather days in a time period by first quantifying the number of each month (EX: January: 3, Feburary: 3, March: 2, etc...) between two dates (Start Date: 4/15/16, End Date: 11/28/19) I want to populate the cells next to each respective month in my DATE CALCULATOR within my spreadsheet. I have not found any way to accomplish this and need help. 

By counting the number of times, a month appears between two dates I can then multiply that number by the historical data of weather days for each respective month, giving me total amount of weather days between two dates.

[#2]

Hi @CollinRosser 

You can use the below formula to quantify how many times a month comes in a given date range.

=IF(YEAR($G$14)<YEAR($I$16),IF(VLOOKUP(F18,$F$34:$G$45,2,0)<VLOOKUP((TEXT($G$14,"MMMM")),$F$34:$G$45,2,0),0,1),IF(AND(VLOOKUP(F18,$F$34:$G$45,2,0)>=VLOOKUP((TEXT($G$14,"MMMM")),$F$34:$G$45,2,0),VLOOKUP(F18,$F$34:$G$45,2,0)<=VLOOKUP((TEXT($I$16,"MMMM")),$F$34:$G$45,2,0)),1,0))+IF(((YEAR($I$16))-(YEAR($G$14))-1)<0,0,((YEAR($I$16))-(YEAR($G$14))-1))+IF(YEAR($I$16)>YEAR($G$14),IF(VLOOKUP(F18,$F$34:$G$45,2,0)>VLOOKUP((TEXT($I$16,"MMMM")),$F$34:$G$45,2,0),0,1),0)

You may also refer to the attached file wherein I have also copied this formula in your Date Calculator.

I have also included a 'Working' sheet in the same file that may help you understand the logic.

Please let me know if it works for you.

Thanks

Tauqeer

[#3]

@CollinRosser 

You can use the follow approach. You just need to update the Month Index and the days of the month in the formula.

=SUMPRODUCT(N(MONTH(ROW(INDIRECT($G$14&":"&$I$16)))=MonthIndex))/NumberOfDays

1=Jan

31=Total of days of Jan

=SUMPRODUCT(N(MONTH(ROW(INDIRECT($G$14&":"&$I$16)))=1))/31

[#4]

@CollinRosser 

As variant

=YEAR($I$16) - YEAR($G$14) +
 ( (YEAR($G$14)*100 + MONTH(1&F18) ) >= (YEAR($G$14)*100+MONTH($G$14) )) *
 ( (YEAR($I$16)*100 + MONTH(1&F18) ) <= (YEAR($I$16)*100+MONTH($I$16) ))

here

and copy/paste to other cells.