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

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

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

Resources