Forum Discussion
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
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.
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))/31Hi 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
