formula to get previous week Monday's date

[ashani]

Hi experts,

I wonder if someone could help me please, I'm looking for a formula to get Previous week's Mondays date automatically based on the date in the cell A1.

Your help will be much appreciated.

Many thanks,

 

[Joe4]

Try this:

=A1-WEEKDAY(A1,3)-7

 

[Fluff]

How about

=WORKDAY.INTL(A1-WEEKDAY(A1,2),-1,"0111111")

 

[Bosquedeguate]

Formula is DateCell - (WEEKDAY(DateCell) + 5)
If Cell A1 contains a date
In Cell A2 enter =A1 - (WEEKDAY(A1)+5)

GetLastRow.xlsm
	A	B	C	D
53	DATE	DOW	PREV MON	NEW DATE DOW
54	7/18/2023	3	7/10/2023	2
55	7/19/2023	4	7/10/2023	2
56	7/20/2023	5	7/10/2023	2
57	7/21/2023	6	7/10/2023	2
58	7/22/2023	7	7/10/2023	2
59	7/23/2023	1	7/17/2023	2
60	7/24/2023	2	7/17/2023	2
Sheet1

Cell Formulas
Range	Formula
B54:B60,D54:D60	B54	=WEEKDAY(A54)
C54:C60	C54	=A54 - (5+WEEKDAY(A54))

 

[ashani]

Perfect - thank you so much for your prompt reply.

 

[Joe4]

Perfect - thank you so much for your prompt reply.

You are welcome.

I assume you mean replies, as you actually have 3 replies/feasible solutions!

 

[Bosquedeguate]

Note the WORKDAY.INTL and WEEKDAY Functions provided above failed when the source date is a Sunday.

DATE	DOW	PREV MON	NEW DATE DOW	=WEEKDAY Function	NEW DATE DOW	=WORKDAY.INTL Function	NEW DATE DOW
7/18/2023	3	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/19/2023	4	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/20/2023	5	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/21/2023	6	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/22/2023	7	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/23/2023	1	7/17/2023	2	7/10/2023	2	7/10/2023	2
7/24/2023	2	7/17/2023	2	7/17/2023	2	7/17/2023	2

 

[Joe4]

Note the WORKDAY.INTL and WEEKDAY Functions provided above failed when the source date is a Sunday.

DATE	DOW	PREV MON	NEW DATE DOW	=WEEKDAY Function	NEW DATE DOW	=WORKDAY.INTL Function	NEW DATE DOW
7/18/2023	3	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/19/2023	4	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/20/2023	5	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/21/2023	6	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/22/2023	7	7/10/2023	2	7/10/2023	2	7/10/2023	2
7/23/2023	1	7/17/2023	2	7/10/2023	2	7/10/2023	2
7/24/2023	2	7/17/2023	2	7/17/2023	2	7/17/2023	2

Ah yes, good catch! I missed that.

 

[Fluff]

That just depends on what is meant by "the previous week". To me Sunday is the end of the week (hence the weekend) so the correct answer would be the 10th & not the 17th.