Backdate to specific day of week

[LeonardH]

Hello Everyone,

This might be simple. I just need a formula to backdate to the Thursday before last from whatever day of the week today is. For example, if today is Monday, I need the date of the Thursday before last. If today is Friday, I need the date of the Thursday before yesterday. I hope this make sense.

Thanks!

 

[Alex Blakenburg]

I am not 100% clear on which Thursday you want, so see if this is what you mean and we can go from there.

Book1
	A	B
1
2	Base Date	Thu before
3	Mon 07-Mar-2022	Thu 03-Mar-2022
4	Tue 08-Mar-2022	Thu 03-Mar-2022
5	Wed 09-Mar-2022	Thu 03-Mar-2022
6	Thu 10-Mar-2022	Thu 03-Mar-2022
7	Fri 11-Mar-2022	Thu 03-Mar-2022
8	Sat 12-Mar-2022	Thu 03-Mar-2022
9	Sun 13-Mar-2022	Thu 03-Mar-2022
10
Sheet1 (2)

Cell Formulas
Range	Formula
B3:B9	B3	=A3-WEEKDAY(A3,2)-7+4

 

[BSALV]

i think you ask column C

Map1
	A	B	C
1	date	last Thursday	2nd last
2	wo 02/03	do 24/02	do 17/02
3	do 03/03	do 03/03	do 24/02
4	vr 04/03	do 03/03	do 24/02
5	za 05/03	do 03/03	do 24/02
6	zo 06/03	do 03/03	do 24/02
7	ma 07/03	do 03/03	do 24/02
8	di 08/03	do 03/03	do 24/02
9	wo 09/03	do 03/03	do 24/02
10	do 10/03	do 10/03	do 03/03
11	vr 11/03	do 10/03	do 03/03
12	za 12/03	do 10/03	do 03/03
13	zo 13/03	do 10/03	do 03/03
14	ma 14/03	do 10/03	do 03/03
15	di 15/03	do 10/03	do 03/03
16	wo 16/03	do 10/03	do 03/03
17	do 17/03	do 17/03	do 10/03
18	vr 18/03	do 17/03	do 10/03
19	za 19/03	do 17/03	do 10/03
20	zo 20/03	do 17/03	do 10/03
21	ma 21/03	do 17/03	do 10/03
22	di 22/03	do 17/03	do 10/03
Blad3

Cell Formulas
Range	Formula
A2:A22	A2	=SEQUENCE(21,,TODAY())
B2:B22	B2	=A2-WEEKDAY(A2,14)+1
C2:C22	C2	=B2-WEEKDAY(B2,14)-6
Dynamic array formulas.

 

[Phuoc]

Try this:

=WORKDAY.INTL(A1,-1,"1110111")

 

[Fluff]

Maybe one of these

+Fluff 1.xlsm
	A	B	C	D
1	Base Date	Thu before
2	Mon 07-Mar-22	Thu 03-Mar-22	Thu 03-Mar-22	Thu 24-Feb-22
3	Tue 08-Mar-22	Thu 03-Mar-22	Thu 03-Mar-22	Thu 24-Feb-22
4	Wed 09-Mar-22	Thu 03-Mar-22	Thu 03-Mar-22	Thu 24-Feb-22
5	Thu 10-Mar-22	Thu 03-Mar-22	Thu 10-Mar-22	Thu 24-Feb-22
6	Fri 11-Mar-22	Thu 10-Mar-22	Thu 10-Mar-22	Thu 03-Mar-22
7	Sat 12-Mar-22	Thu 10-Mar-22	Thu 10-Mar-22	Thu 03-Mar-22
8	Sun 13-Mar-22	Thu 10-Mar-22	Thu 10-Mar-22	Thu 03-Mar-22
Data

Cell Formulas
Range	Formula
B2:B8	B2	=WORKDAY.INTL(A2,-1,"1110111")
C2:C8	C2	=WORKDAY.INTL(A2+1,-1,"1110111")
D2:D8	D2	=WORKDAY.INTL(A2,-2,"1110111")