Total manhours in date range

First unmerge all the cells in row 1 and put 2017 and 2018 in each respective cell.

Then result area should also unmerge

then try

J38[FORMULA=SUM(IF(DAY(H36)<=A3:A33,OFFSET(A2,1,MATCH(TEXT(H36,"MMM-YYYY"),B2:Y2&"-"&B1:Y1,0),31,),""),IF(DAY(L36)>=A3:A33,OFFSET(A2,1,MATCH(TEXT(L36,"MMM-YYYY"),B2:Y2&"-"&B1:Y1,0),31,),""),IFERROR(OFFSET(A2,1,MATCH(TEXT(H36,"MMM-YYYY"),B2:Y2&"-"&B1:Y1,0)+1,31,MATCH(TEXT(L36,"MMM-YYYY"),B2:Y2&"-"&B1:Y1,0)-MATCH(TEXT(H36,"MMM-YYYY"),B2:Y2&"-"&B1:Y1,0)-1),0))[/FORMULA]

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Check the attached file.

Another proposition:
Cells in row 2 shall contain dates 2017-01-01 2017-02-01 etc and only be formatted to display month names (custom formatting "mmm")
Then formula in J38:

Formula:

Please Login or Register  to view this content.

would do the job

PS. Note that I used day numbers starting from empty cell (treated as 0 in A2, not A3:A33 - it is because upper left corner A3+B2 would be already Jan 2nd etc. while A2+B2=Jan 1st
PS2. I started with much more complicated and (as it shows - unnecessary complicated formula first creating 31 by 24 arrays using MMult -

Formula:

Please Login or Register  to view this content.

@ Kaper,

I really like your approach. One thing I noticed. If there are data entry errors ... for example data entered in 6/31 your formula will interpret this (correctly) as 7/1. Hours would get double counted. End user would have no clue it happened. February could be worse.

If I may be permitted a modification of your SUMPRODUCT formula ...

Formula:

Please Login or Register  to view this content.

This is working at my end and ignores data entry errors.

PS I forgot to mention it must be array entered.

Hi Dave,

Good point, but it leads to quite an increase in formula length and complication.
May be it would be wise to unblock only "real dates" and leave blocked (default state) 30 Feb, 31 Apr etc. Then protect the sheet (even with no password).

See attached.

PS. Thanks for reputation point.

Kaper, yes, I found the change complicating as well. I debated whether to post it.

Cool unblocking solution in the upload. Much preferable to the formula I posted.
I wonder if there is a VBA unblocking routine that could account for leap years as well. Unfortunately I know ~0 about VBA.

PS. Thanks for the rep.