Excel - count days in each month between two dates

问题:

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

回答1:

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)

回答2:

Use SUMPRODUCT:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))

This will iterate through the days and match the text of the three letter month to the headers.

This assumes that the header is text and not a date that is formatted mmm.

The ROW(INDIRECT($A2 & ":" & $B2)) is as close to a For Loop that we can get with formulas. It will iterate 1 day at a time. Because dates are stored as numbers in excel with one day being 1 we can iterate from the start date to the end date.

The SUMPRODUCT is an array type formula, it is what causes the formula to loop and does the count.

The (TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1) returns TRUE/FALSE for each iteration, while the -- converts that to 1/0.

The SUMPRODUCT then adds all the 1/0 to get the count.

---

If the headers are dates formatted as mmm then use this instead:

=SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))))=MONTH(C$1)))