Excel - count days in each month between two dates

Question

I am trying to write an excel formula which can be dragged across a row of cells to give the number of days in each month between two specified dates. E.g:

         A           B         C       D       E       F 

1  | START DATE | END DATE |  Jan  |  Feb  |  Mar  |  Apr  |...
   ---------------------------------------------------------
2  |  10/02/17  | 15/03/17 |   0   |  19   |  15   |   0   |...

An added complication is that the end date may be left blank indicating that the end date should be treated as today. I can put a formula in the cells starting from C2 but columns A & B must be left for user entry.

I made some progress using lots of nested if statements but am having real trouble getting it to handle if the end date is blank.

Can anyone help with this please?

*Edit

I refrained from showing the formula I had written so far as I'm afraid it's a bit messy but as it has been requested, I have now shown below (this formula from C2)

 =IF(AND($A2<C$1,$B2<D$1),DAY(A2),IF($A2>=D$1,0,IF(AND($A2<C$1,$B2=""),IF(TODAY()<C$1,0,IF(TODAY()>C$1,IF(TODAY()<D$1,DAY(TODAY()),D$1-C$1),IF(TODAY()<D$1,INT(TODAY()-$A2),$D1-$C1))),IF(AND($A2>=C$1,$B2=""),IF(TODAY()<D$1,INT(TODAY()-$A2+1),D$1-$A2),IF(AND($A2>=C$1,$B2<D$1),$B2-$A2+1,IF(AND($A2<C$1,$B2<C$1),0,IF(AND($A2>=C$1,$B2>=D$1),D$1-$A2,IF(AND($A2<C$1,$B2<D$1),DAY(B2),IF(AND($A2<C$1,$B2>=D$1),D$1-C$1,0)))))))))

Thanks for the comment. Yes, the first day should count, I've edited my question — Leroy
Also are we allowed to assume start date is no more than a year before end date? — Jeremy Kahan
Well it'd be useful to see the formula you have so far; but just at a guess, replace every occurence of [End Date] in your formula with IF(ISBLANK([End Date], TODAY(), [End Date]) should handle the blanks. Or a formula I like to use if [End Date] is some complex function rather than a cell reference (like in the IF statement above) is 1/IFERROR(1/[End Date], 1/TODAY()) as this only uses [End Date] once as opposed to twice in the other formula (but it's arguably less readable). It's always better to post the code you've written rather than just to try and get users to write it for you — Greedo
I honestly think it would, there's a lot of clever people on this site who can read pretty much any formula, give it a go! But try to limit what you post only to show what's relevant (see the help centre). Also consider IFS( (office 365 2016) when using multiple nested if statements, or CHOOSE( if IFS isn't an option — Greedo

Tom Sharpe Tom Sharpe · Accepted Answer · 2017-06-12T19:45:10

Anyway here is the alternative approach for completeness based on the standard formula

=max(0,min(end1,end2)-max(start1,start2)+1)

for the overlap between 2 date ranges which gives

=MAX(0,MIN(IF($B2="",TODAY(),$B2),EOMONTH(DATEVALUE(C$1&"-17"),0))-MAX($A2,DATEVALUE(C$1&"-17"))+1)

Excel - count days in each month between two dates

2 Answers