excelexcel-formulaexcel-dates

How do I find certain days of a month between two dates in Excel using formula?


Lets say I have two dates: 2024-01-16 and 2024-03-16. On 15th of each month an event happens. In this case it happened 2 times (on 2024-02-15 and 2024-03-15).

How do I find number of events happened between dates using Excel formula?


Solution

  • Try something along the lines of using SEQUENCE() function:

    enter image description here


    • Formula used in cell B4

    =SUM(N(DAY(SEQUENCE(B2-B1+1,,B2))=B3))
    

    If using older versions then could try the following:

    =SUMPRODUCT(N(DAY(B1+(ROW($ZZ$1:INDEX($Z:$Z,B2-B1+1))-1))=B3))