CODEWITHSUNDEEP
excelexcel-formula
Victor Sundelin
Victor Sundelin

Reputation: 1

Calculate number of days between two dates for each month and year - Excel

Found a satisfying answer for calculating number of days for each month but summarized when more than 1 year in this post:

Excel - count days in each month between two dates

I used this formula proposed by Scott Craner:

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

When the start date and end date is more than 1 year apart the function starts adds up the total of days from all days from the same months, but i want the days to group by month and year.

At the moment the function works like this:

What i want is to be able to also split the calculation over years, so the result in the example above would be:

Anyone that can help me to add the distinction for years as well to that formula?

I have tried to add YEAR in the formula but i cant seem to get it to work properly.

Thanks, Victor

Upvotes: 0

Views: 744

Answers (3)

P.b
P.b

Reputation: 11415

Using Office 365:

=LET(s,TEXT(SEQUENCE(1+B2-A2,,A2),"mm-e"),
     u,UNIQUE(s),
HSTACK(u,
       MMULT(N(TOROW(s)=u),
             SEQUENCE(ROWS(s),,,0))))

This spills the month-year combinations in given range and it's count:

enter image description here

Upvotes: 0

nkalvi
nkalvi

Reputation: 2614

Something like this perhaps (in Excel 2021 and later):

Format the first column with 'mmmmyyyy'

=LET(
    start, B1,
    end, B2,
    num_months, YEARFRAC(EOMONTH(start, 0), EOMONTH(end, 0)) *
        12 + 1,
    month_offset, SEQUENCE(num_months, , 0),
    months, DATE(YEAR(start), MONTH(start) + month_offset, 1),
    num_days, IFS(
        month_offset = 0,
        EOMONTH(start, 0) - start + 1,
        month_offset = num_months - 1,
        DAY(end),
        TRUE,
        DAY(
            EOMONTH(
                DATE(
                    YEAR(start),
                    MONTH(start) + month_offset,
                    1
                ),
                0
            )
        )
    ),
    HSTACK(months, num_days)
)

Result

Upvotes: 1

rachel
rachel

Reputation: 1994

I think you can just do it like this: enter image description here

Formula in C2:

=MIN($B$2,EOMONTH(DATE(YEAR(C1),MONTH(C1),1),0))-MAX($A$2,DATE(YEAR(C1),MONTH(C1),1))+1

Can be dragged to the right.

The idea is using min(end_date, month_end) - max(start_date, month_start) to count number of days for a given month within start_date and end_date.

Upvotes: 3

Related Questions