Forum Discussion

wahidfajar's avatar
wahidfajar
Copper Contributor
Dec 14, 2022
Solved

Calculate days elapsed between 2 duration dates based on 2 cut off dates on a month

Hi, I'm working on calculating days between date duration based on 2 cutoff dates on a specific month. I just need the days between duration and cut off based on month in row $B$ to be filled on e...
admin
excel
Formulas and Functions
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Dec 15, 2022

    wahidfajar 

    =IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",MAX(MIN(Table1[@[Finish]:[Finish]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11))-MAX(Table1[@[Start]:[Start]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-12)),0))

HansVogelaar's avatar
HansVogelaar
MVP
Dec 15, 2022

wahidfajar 

That would be

 

=MAX(MIN([@Finish],DATE(2023,1,1))-MAX([@Start],DATE(2022,1,1)),0)

wahidfajar's avatar
wahidfajar
Copper Contributor
Dec 15, 2022

Hi HansVogelaar 

I have next problem with summarize days between 2 formula you solve before this. (The file is attached).

 

So, there are innumerable durations of each dates on blue table based on cutoff in the yellow table.
I need the result between cummulative days and cummulative on every week to produce the same result.

 

Can you help me for this?