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))
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?
Column G (Full Cumvmulative Days) counts the total number of days for the entire year 2022, not just for the month in column B (Month).
The numbers in columns H to L (Com Week 1 to Com Week 5) are for the weeks of the month in column B only. So their sum is much less than that of column G.
Would you prefer the numbers in column G to be for the specified month only, instead of for the entire year?
this is my thinking I describe in sql.
re:
cummulative days and cummulative on every week to produce the same result.
need to clear
March 5 A 2022/1/15 13:00 2022/3/9 17:00
row 7
2022/1/15 13:00 2022/3/9 17:00
cross Jan Feb and Mar 3 months?
is not it right to need split one row to 3 rows for holding this activity A?
