Excel - count days in each month between two dates (both dates available)

Question

I have found the solution thought it would be good to share:

so the formula should be : =((MAX(CT$6-$N8,0)-MAX(EOMONTH(CT$6,-1)-$N8,0))-(MAX(CT$6-$O8,0)-MAX(EOMONTH(CT$6,-1)-$O8,0))+(EOMONTH(CT$6,0)=EOMONTH($N8,0)))*1

:)

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:

I tried using a if with sum product formula from the same website but for some reason it is not recognising the days of last month. was wondering if someone could help explain what this formula does and how to correct it.

=IFERROR(IF(AND(CS$6>=$M9,CS$6<=$N9),SUMPRODUCT(--(MONTH(ROW(INDIRECT($M9&":"&IF($N9="",TODAY(),$N9))))=MONTH(CS$6))),),"")

Answer 1

SECOND REVISION (R2)

• Updated workbook link to capture fixed column headings
• Eqn / formula also includes static view

Depending upon years spanned, R2 represents a less favourable outcome re space (will require more columns). In this case, number of additional columns required to match stacked view ~100 (!!).

FIRST REVISION (R1) etc. based upon @Charlotte.Sarah.Smith feedback

• Days in each month were 1 day less than they should have been - this has been corrected (in both screenshot, linked sheet, and relevant eqns)
• Also showcase a couple of sample illustrations/visual representations which may/not be of interest (included within linked sheet too)

---

Suppose we wanted to expand upon @Will's solution by stacking the dates by year, so that column headings can vary according to different start dates (as opposed to the very first start date that happens to appear)...

For instance, if the next row included the date range 'start = 10/02/16' through 'end = 15/03/19' - you'll appreciate that the number of columns spanning Jan-Dec ('16), Jan-Dec ('17),... up to (and incl.) the range in the first row (Jan '21 - Mar '23) becomes unwieldy.

---

By using a data table (see here) you can produce a 'stacked' view of the number of days per month regardless of the year (!) - see screenshot below and link to this [updated/corrected] worksheet.

R2 screenshot:

---

REVISION

See validation/reconciliation column at end

*Visual representations - could be useful for assessing trends etc.

---

Fun in 3D too!

1] Red font: first row that defines the construct of the data table: enter date range in the format '10/02/2021-15/08/2023'

Cell E3 eqn (drag to right):

=IFERROR(IF($A3>EDATE(E$2,1),0,MAX(,IFERROR(IF(MIN($B3,MIN(EDATE(E$2,1),$B3))=E$2-1,0,MIN($B3,MIN(EDATE(E$2,1),$B3))),"")-MAX($A3,E$2))),0)

(Similar to what we've seen previously)

2] Table below red font: enter any start date (as a regular date, e.g. dd/mm/yyyy) < end date (likewise, regular date) in columns A, B as desired/req.

---

Data Table

3] Data Table (column data): enter the following in cell c4 (drag down as req.)

=TEXT(A4,"dd/mm/yyyy")&"-"&TEXT(B4,"dd/mm/yyyy")

4] Data Table (highlight cells c3:d42, insert data table, select blank/empty cell for 'row input' and c2 for 'column input')

The data table should 'come to life' (calculate sheet, shift + F9) otherwise.

FilterXML

5] Split result by delimiter '|' using FilterXML as follows (cell E4, only drag down, not to right):

=IFERROR(TRANSPOSE(FILTERXML("<AllText><Num>"&SUBSTITUTE(LEFT(MID(D4,2,LEN(D4)-1),LEN(MID(D4,2,LEN(D4)-1))-1),"|","</Num><Num>")&"</Num></AllText>","//Num")),"")

VALIDATION

Note the check column: date difference = sum of days in table (default cell colour is otherwise RED):

REVISION 2

Here is the formula for a static version of above (i.e. no stacking by year, instead, each date in column headers are distinct re calcs) - it was already available in row with red font(!!)

=IFERROR(IF($A3>EDATE(C$2,1),0,MAX(,IFERROR(IF(MIN($B3,MIN(EDATE(C$2,1),$B3))=C$2-1,0,MIN($B3,MIN(EDATE(C$2,1),$B3))),"")-MAX($A3,C$2))),0)

Viola!

Answer 2

One potential solution would be to compare dates using the MIN and MAX functions like this:

=IF(MIN($B2+1,D$1)-MAX($A2,C$1)<0,"",MIN($B2+1,D$1)-MAX($A2,C$1))

screenshot

This solution uses month starting dates (e.g., Feb 1, 2021), but it could be adapted to work with month end dates instead (e.g., Feb 28, 2021).

The idea is that you take the later of the finish date or month end date minus the lesser of the start date or the month start date. For the first month and last months, this returns the number of days within that range inclusive of the first and last day. For all the months between the first and last months, it returns the number of days in the month.

The +1 on the minimum of the end dates seems a little counterintuitive, but it's necessary to include the first and last day in the count. Without it, you'd get 14 days in March 2022 instead of 15.

The "if less than zero" function makes it return a blank (or zero if you'd prefer) for any month outside the dat range.

This solution does not summarize by month (e.g., 19 days for Feb 2021 plus 28 days for 2022 to get 47 total days in February), but that's consistent with the sample you provided. The comments also indicate that you want to consider the years as well.