Counting the number of each month between two dates?

Occasional Visitor

Counting the number of each month between two dates?

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.

3 Replies

Re: Counting the number of each month between two dates?

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

Re: Counting the number of each month between two dates?

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``

Re: Counting the number of each month between two dates?

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.