Calculate 3 days back but skipping weekends

0 votes

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.

enter image description here

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

Jan 23 in Others by Kithuzzz
• 35,840 points
65 views

1 answer to this question.

0 votes

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)))
)
answered Jan 23 by narikkadan
• 61,360 points

Related Questions In Others

0 votes
1 answer

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

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

answered Nov 8, 2022 in Others by gaurav
• 22,970 points
141 views
0 votes
1 answer

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

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

answered Apr 6, 2020 in Others by Niroj
• 82,840 points
1,134 views
0 votes
1 answer

How to Navigate to a new screen and back?

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

answered Aug 26, 2020 in Others by MD
• 95,440 points
328 views
0 votes
1 answer
0 votes
1 answer

How to split text values by a delimiter?

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

answered Oct 11, 2022 in Others by narikkadan
• 61,360 points
185 views
0 votes
1 answer

Currency conversion:number to words excel

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

answered Oct 28, 2022 in Others by narikkadan
• 61,360 points
113 views
+1 vote
1 answer

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

answered Dec 24, 2022 in Others by narikkadan
• 61,360 points
122 views
0 votes
1 answer

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

answered Jan 9 in Others by narikkadan
• 61,360 points
48 views
0 votes
1 answer

Excel remaining days count from today until due date minus weekends?

The problem is that you are not ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 61,360 points
85 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP