Search code examples
excelexcel-formulasumproduct

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

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:

enter image description here

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

Solution

  • 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.

    R1,stacked days by month chart

    R2 screenshot:

    R2 - Update

    REVISION

    See validation/reconciliation column at end

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

    2D and 3D illustrations

    Fun in 3D too!

    enter image description here

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

    Check column included as part of revised / corrected solution - all in order ☺

    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!