Excel Formula Last Business of each week

[ItalianPlatinum]

Hi, looking for an excel formula that will kick back the last business day of each week. for example:

Today Expect result
4/21/21 4/23/21
3/31/21 4/01/21 (good Friday on 04/02/21)

 

[auto.pilot]

Consider using WEEKDAY in a formula. For example, if today's date is in cell C3, use =WEEKDAY(C3) in another cell. The result will be 4 because 4/21/21 is the 4th day of the week. Perhaps this will help.

 

[ItalianPlatinum]

Consider using WEEKDAY in a formula. For example, if today's date is in cell C3, use =WEEKDAY(C3) in another cell. The result will be 4 because 4/21/21 is the 4th day of the week. Perhaps this will help.

How though. because the last BD of the week wont always be Friday if there is a Friday holiday.

 

[auto.pilot]

The formula I posted identifies Friday when the result = 6, which is the standard last business day of the week. I am not aware of any formula that identifies holidays that exist on a Friday.

 

[Fluff]

How about

+Fluff 1.xlsm
	H	I	J	K
1				Holidays
2	21/04/2021	23/04/2021		01/01/2021
3	31/03/2021	01/04/2021		02/04/2021
4				05/04/2021
5
6
Main

Cell Formulas
Range	Formula
I2:I3	I2	=WORKDAY.INTL(WORKDAY.INTL(H2-1,1,"0111111"),-1,1,$K$2:$K$5)

 

[ItalianPlatinum]

Thanks. So far works

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Habtest]

How about

+Fluff 1.xlsm
	H	I	J	K
1				Holidays
2	21/04/2021	23/04/2021		01/01/2021
3	31/03/2021	01/04/2021		02/04/2021
4				05/04/2021
5
6
Main

Cell Formulas
Range	Formula
I2:I3	I2	=WORKDAY.INTL(WORKDAY.INTL(H2-1,1,"0111111"),-1,1,$K$2:$K$5)

Hi @Fluff , Thanks for the answer, I learnt something new, as usual . Though, I think it doesn't work with Sundays and Mondays, in which case it returns the last workday in the previous week. Should be corrected as below right?
=WORKDAY.INTL(WORKDAY.INTL(H2-1,1,"1111101"),-1,1,$K$2:$K$5)

Test.xlsx
	K	L	M	N	O	P	Q	R	S
1	Holidays		Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	3/7/2021		7-Mar	8-Mar	9-Mar	10-Mar	11-Mar	12-Mar	13-Mar
3	3/15/2021		14-Mar	15-Mar	16-Mar	17-Mar	18-Mar	19-Mar	20-Mar
4	3/23/2021		21-Mar	22-Mar	23-Mar	24-Mar	25-Mar	26-Mar	27-Mar
5	3/31/2021		28-Mar	29-Mar	30-Mar	31-Mar	1-Apr	2-Apr	3-Apr
6	4/8/2021		4-Apr	5-Apr	6-Apr	7-Apr	8-Apr	9-Apr	10-Apr
7	4/16/2021		11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr
8	4/24/2021		18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr
9
10	"111101"		12-Mar	12-Mar	12-Mar	12-Mar	12-Mar	12-Mar	12-Mar
11			19-Mar	19-Mar	19-Mar	19-Mar	19-Mar	19-Mar	19-Mar
12			26-Mar	26-Mar	26-Mar	26-Mar	26-Mar	26-Mar	26-Mar
13			2-Apr	2-Apr	2-Apr	2-Apr	2-Apr	2-Apr	2-Apr
14			9-Apr	9-Apr	9-Apr	9-Apr	9-Apr	9-Apr	9-Apr
15			15-Apr	15-Apr	15-Apr	15-Apr	15-Apr	15-Apr	15-Apr
16			23-Apr	23-Apr	23-Apr	23-Apr	23-Apr	23-Apr	23-Apr
17
18	"0111111"		5-Mar	5-Mar	12-Mar	12-Mar	12-Mar	12-Mar	12-Mar
19			12-Mar	12-Mar	19-Mar	19-Mar	19-Mar	19-Mar	19-Mar
20			19-Mar	19-Mar	26-Mar	26-Mar	26-Mar	26-Mar	26-Mar
21			26-Mar	26-Mar	2-Apr	2-Apr	2-Apr	2-Apr	2-Apr
22			2-Apr	2-Apr	9-Apr	9-Apr	9-Apr	9-Apr	9-Apr
23			9-Apr	9-Apr	15-Apr	15-Apr	15-Apr	15-Apr	15-Apr
24			15-Apr	15-Apr	23-Apr	23-Apr	23-Apr	23-Apr	23-Apr
Sheet109

Cell Formulas
Range	Formula
M1:S1	M1	=TEXT(WEEKDAY(M8),"ddd")
M3:S8	M3	=M2+7
K2	K2	=M2
K3:K8	K3	=K2+8
M10:S16	M10	=WORKDAY.INTL(WORKDAY.INTL(M2-1,1,"1111101"),-1,1,$K$2:$K$8)
M18:S24	M18	=WORKDAY.INTL(WORKDAY.INTL(M2-1,1,"0111111"),-1,1,$K$2:$K$8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M10:S16	Expression	=M10<>M18	text	NO

 

[Fluff]

Though, I think it doesn't work with Sundays and Mondays, in which case it returns the last workday in the previous week. Should be corrected as below right?

That depends on how you define the last working day of each week. Whilst the Mondays are wrong with my formulae the Sundays are correct (Sunday being the end of the week not the start) the formula should be

=WORKDAY.INTL(WORKDAY.INTL(N2-1,1,"1111110"),-1,1,$K$2:$K$8)