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.

Jan 23 in Others 65 views

## 1 answer to this question.

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)))
)```
• 61,360 points

## Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

## I am trying to run following command But I end up with an error :

Hii Nishant, You are running this command inside ...READ MORE

## How to Navigate to a new screen and back?

Hi@akhtar, Most apps contain several screens for displaying ...READ MORE

## I registered myself for webinar but I cannot attend the webinar due to network problem please tell how can I will get the recording

Hi, @MG As you have mentioned that you ...READ MORE

## How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

## Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

+1 vote

## Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

## Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE