Excel formula question.

Question

I have a schedule spreadsheet that has a complete date and from there I work back several station build dates with a simple =workday formula. what I am trying to do is to come up with an addition formula in addition to that formula to recognize if that date is a Friday and if it is, I want the formula to subtract 1 day to give it a Thursday date. if its not a Friday to leave the date as it is.

so on my spreadsheet column CD21 has a complete date of 1/9/2023, in Column CC21 I have this formula =WORKDAY(CD21-1,sheet3!A1:U1) which just subtracts 1 day from the complete date and accounts for holidays. so in column CC21 I have a date of 1/6/2023 which is a Friday. So I want a formula to add to the existing formula, to recognize it is a Friday by doing a IF/VLOOKUP STEP.

I have a sheet2 that has all of the 2023 Fridays identified in column A and I have an X next to them in Column B. I have tried several combinations with no luck. any help you could provide, I would be very grateful...

this is one of the formulas I have tried. I am by no means an excel expert. =if(CC21=X(VLOOKUP,A2,Sheet2!,A1:B365,2,False)-1, WORKDAY(CD21,-1,1,Sheet3!$A$1:$U$1)

AND THIS IS ANOTHER. =IF COUNTIF(SHEET2!A;A,WORKDAY(CD21,-1,SHEET3!$A$1:$U$1)>0,WORKDAY(CD21,-1)-1,WORKDAY(CD21,-1))

Answer 1

Use

=WORKDAY.INTL(CC21,-1,"0000111",Sheet3!$A$1:$U$1)

The string "0000111" specifies that Friday, Saturday and Sunday are treated as weekend days.