Forum Discussion
Calculate days elapsed between 2 duration dates based on 2 cut off dates on a month
=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))
In G12:
=IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",ROUNDUP(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),0))
Format as General.
Fill to the right, and down if Excel doesn't do that automatically.
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?=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))
Hi HansVogelaar
I have the further problem with date calculation.
Now, I need a formula to calculate only days passed on 2022 on all completed activity. In this problem I don't have any other table but only this 1.
Would you mind helping with this problem?
Thank you
