Calculate 3 days back but skipping weekends

Question

We track when a coworker has contributed to a project on a spreadsheet. This has several columns for the projects after a column for the dates. In the projects cell, we mark an x for each time someone worked on a project.

The projects column will now be projected backward by three days, excluding weekends. In essence, the project was worked on Friday the same way on Thursday, Wednesday, and Tuesday. The end product ought to resemble this:

		Project A	Result
2023-01-01	Weekend
2023-01-02	Weekday		X
2023-01-03	Weekday		X
2023-01-04	Weekday		X
2023-01-05	Weekday	X	X
2023-01-06	Weekday		X
2023-01-07	Weekend
2023-01-08	Weekend
2023-01-09	Weekday		X
2023-01-10	Weekday	X	X
2023-01-11	Weekday

I hope this example is clear enough. Date 2023-01-05 has an X (or a TRUE) in the 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 in 2023-01-10, 2023-01-09, 2023-01-06, 2023-01-05.

I made three columns, one of which checks the cell one day. A second one checked the day two days prior, and so on. After that, create a new column by using the OR algorithm to see if any of these columns are true. Obviously, this doesn't address the weekend issue.

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

Answer 1

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)))
)