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))
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 youHi 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?
- Wow its works perfectly. You rock man! Thank you
I'll need more help on excel, may be I'll catch you again in this post later. Again, thank you sir!