SUM IF start date and end date are found in a particular month

[Alecs]

Hello, here is a tricky one

does anyone have an solution for this formula? I attach the excel file bellow

here is how should work:

I will insert in table1 the Name, the Start date and the End date for my booking. There can be up to 100 different bookings. Different Names can book same dates.

-> if an booking starts today 21 MAY and finishes today 21 MAY = 1 day

-> if an bookingstarts today 21 MAY and finishes tomorrow 22 MAY = 2 days

-> if another booking starts tomorrow 21 MAY and finishes on 25 MAY = 4 days

___________________

table2 should calculate all the booked days for each Name in every single month without exceeding the todal days of an month. for example MAY: can't have more than 31 booked days. 

So the result for Name A007 in month MAY should be 31 booked days.

and the result for Name A007 in month JUNE should be 6 booked days.

The result for A008 in month JANUARY should be 1 booked day.

and so on...

I've tried an formula with SUMIFS but does not work... If it is easier we can do it in two separated tables

Can you please help me? I must finish it tomorrow and have no solutions remaining.

*** if it is not possible to solve the situation with the aditional counted days (34 days instead of 31 in MAY) let's find a solution that calculates the days only. No matter if are above 31 in a month. But will be nice and correct to count only the maximum days in a month.

Thanks,

Alex

[#2 · Accepted solution]

@Alecs 

In I3:

=MIN(SUMPRODUCT(($A$3:$A$37=$H3)*IF(IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)>=0,IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)+1,0)),EOMONTH(I$2,0)-I$2+1)

If you have Microsoft 365 or Office 2021, the following is more readable:

=LET(
    Names,$A$3:$A$37,
    TheName,$H3,
    StartDate,$B$3:$B$37,
    EndDate,$C$3:$C$37,
    StartMonth,I$2,
    EndMonth,EOMONTH(StartMonth,0),
    DaysInMonth,EndMonth-StartMonth+1,
    ActualStart,IF(StartDate>=StartMonth,StartDate,StartMonth),
    ActualEnd,IF(EndDate<=EndMonth,EndDate,EndMonth),
    Duration,ActualEnd-ActualStart,
    ActualDuration,IF(Duration>=0,Duration+1,0),
    Total,SUMPRODUCT((Names=TheName)*ActualDuration),
    MIN(Total,DaysInMonth)
)

[#3]

@Hans Vogelaar
Hans, as always... very helpful and good-hearted man. This is exactly what I've needed. I send you my best wishes. Many thanks!
Alex

[#4]

@Hans Vogelaar 

I've forgot about an important step.
Is it possible to do the same calculation with the same formula that has one more condition? I do not understand your formula to be able to reproduce it in this way.
I have added column B with Confirmation: YES or NO.
So, if it is YES than do the calculation. IF it is another status than don't do it. I've attached here the updated file.

Let's use this formula because other users that I work with have the 2019 excel version
=MIN(SUMPRODUCT(($A$3:$A$37=$H3)*IF(IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)>=0,IF($C$3:$C$37<=EOMONTH(I$2,0),$C$3:$C$37,EOMONTH(I$2,0))-IF($B$3:$B$37>=I$2,$B$3:$B$37,I$2)+1,0)),EOMONTH(I$2,0)-I$2+1)

Best regards,
Alex

[#5]

@Alecs 

It would be

=MIN(SUMPRODUCT(($A$3:$A$37=$I3)*($B$3:$B$37="YES")*IF(IF($D$3:$D$37<=EOMONTH(J$2,0),$D$3:$D$37,EOMONTH(J$2,0))-IF($C$3:$C$37>=J$2,$C$3:$C$37,J$2)>=0,IF($D$3:$D$37<=EOMONTH(J$2,0),$D$3:$D$37,EOMONTH(J$2,0))-IF($C$3:$C$37>=J$2,$C$3:$C$37,J$2)+1,0)),EOMONTH(J$2,0)-J$2+1)

[#6]

It worked. Many thanks!