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

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

Book112.xlsx15 KB

excel

Formulas and Functions

HansVogelaar

May 21, 2022

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

5 Replies

HansVogelaar
MVP
May 21, 2022
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) )
- Alecs
  Brass Contributor
  May 30, 2022
  HansVogelaar
  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
  Book112.xlsx16 KB
  - HansVogelaar
    MVP
    May 30, 2022
    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)
- Alecs
  Brass Contributor
  May 21, 2022
  HansVogelaar
  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

Forum Discussion

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

5 Replies

Resources