We have a spreadsheet where we keep track of when a coworker has worked on a specific project. This contains a column for the dates, and then a bunch of columns for the projects. Whenever someone worked on a project, we write an x in the projects cell.

Now we'd like to project the projects column into the past by 3 days, but skipping the weekends. Essentially when someone worked on the project on Friday, they also worked on it, Thursday, Wednesday, and Tuesday. The result should look like this:

I hope this example is clear enough. Date 2023-01-05 has an X (or a TRUE) in Project A column. We can therefore fill 2023-01-05 until 2023-01-02 with X's because there's no weekend in between. 2023-01-10 has an X, but when we try to go back, there's a weekend, and so we have to fill 2023-01-10, 2023-01-09, 2023-01-06, 2023-01-05.

I tried making three columns, one check the cell back one day. Another one check the day 2 days ago, and so forth. Then using the OR formula to check whether any of these columns is true to generate a new column. This obviously doesn't solve the weekend problem.

Pointers in the right direction for Google Sheets would be nice, but Excel is also good for me.

CodePudding user response：

For Office 365, assuming the Date and Project A ranges are A2:A12 and C2:C12 respectively:

=LET(
    ζ,A2:A12,
    ξ,C2:C12,
    REPT("X",1-ISNA(MATCH(ζ,TOCOL(FILTER(WORKDAY( ζ,-SEQUENCE(,4,0)),ξ="X")),0)))
)

CodePudding user response：

in GS:

=INDEX(IF(""<>IFNA(VLOOKUP(A3:A, FLATTEN(LAMBDA(x, 
 ARRAY_CONSTRAIN(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(REGEXMATCH(WEEKDAY(x, 2)&"", "6|7"),,x)),,9^9))), " "), 9^9, 4))
 (FILTER(A:A, C:C="X")-SEQUENCE(1, 6, ))), 1, )), "X", ))

CodePudding user response：

within GSheets you can try:

=INDEX(IF(LEN(A2:A),LAMBDA(z,IF(LEN(XLOOKUP(A2:A,z,z,)),"x",))(FLATTEN(MAP(A2:A,B2:B,lambda(ax,bx,IF(bx<>"x",,TRANSPOSE(ARRAY_CONSTRAIN(SORT(LAMBDA(z,FILTER(z,NOT(REGEXMATCH(WEEKDAY(z)&"","1|7"))))(offset(ax,-(ROW(ax)-2),0,ROW(ax)-1,1)),1,0),4,1))))))),))