Forum Discussion
Excel formula for availibility overview
=IFERROR(IFERROR(INDEX($C$3:$C$6,MATCH(1,($A9>=$B$3:$B$6)*($A9<=$F$3:$F$6)*(B$8=$E$3:$E$6),0)),INDEX($C$3:$C$6,MATCH(1,($A9>=$B$3:$B$6)*($A9<=$F$3:$F$6)*(B$8=$D$3:$D$6),0))),"")
You can try this formula along with a helper column which is in range F3:F6 in this example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is filled across range B9:H22.
This is the formula in cell F3:
=B3+A3-1
- Thank you very much. I will try that out if I can`t move on with the other tip.
But I appreciate the effort, thanks und Grüße 😉=LET(dates,
UNIQUE(SORT(DROP(REDUCE("",SEQUENCE(ROWS(Samples[Time Planned])),LAMBDA(x,y,VSTACK(x,TAKE(FILTER(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),(WEEKDAY(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),2)<6)),INDEX(Samples[Time Planned],y))))),1))),
lastdate,
SCAN("",SEQUENCE(ROWS(Samples[Time Planned])),LAMBDA(x,y,TAKE(VSTACK(x,TAKE(FILTER(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),(WEEKDAY(SEQUENCE(INDEX(Samples[Time Planned],y)+QUOTIENT(INDEX(Samples[Time Planned],y),7)*2+5,1,INDEX(Samples[Project begin],y),1),2)<6)),INDEX(Samples[Time Planned],y))),-1))),
emp_mach,
TOROW(SORT(UNIQUE(TOCOL(Samples[[Needed employee]:[Needed Ressource]])))),
HSTACK(VSTACK({""},dates),VSTACK(emp_mach,IFNA(MAKEARRAY(COUNTA(dates),COUNTA(emp_mach),LAMBDA(r,c,IFNA(INDEX(Samples[Project description],MATCH(1,(INDEX(dates,r)>=Samples[Project begin])*(INDEX(dates,r)<=lastdate)*(INDEX(emp_mach,c)=Samples[Needed employee]),0)),INDEX(Samples[Project description],MATCH(1,(INDEX(dates,r)>=Samples[Project begin])*(INDEX(dates,r)<=lastdate)*(INDEX(emp_mach,c)=Samples[Needed Ressource]),0))))),""))))
You are welcome. If you work with Office 365 or Excel for the web you can use this formula which excludes saturdays and sundays and dynamically spills the results according to the entries in the dynamic table.
- Thank you so much Oliver, I will try that out 🙂
