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

wahidfajar's avatar
wahidfajar
Copper Contributor
Dec 15, 2022

Thank you for the solution, apreciate it.

But I need precision calculation results of each date duration in table1 (blue).

e.g the duration is:
12/10/2022 16.10 to 09/12/2022 10.00
so the result should be 6.417 days on 1st week December.

Can you help me with this problem?

HansVogelaar's avatar
HansVogelaar
MVP
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))

Resources