Monday and Date addition/subtraction

[Pumpk1n]

Hello.

I have a start date (12/31/21) from which I am adding/subtracting to determine what that date should be. For example, my formula is =WORKDAY($B$1+35,1) or =WORKDAY($B$1-120,1)

What I would really want is the start of the week (Monday). I can't quite nail the formula to add/subtract days to get a Monday start date. Also, if the addition of the number of days brings me to a Wednesday (as an example), I'd want it to be the Monday before versus the Monday following.

Thanks in advance!

 

[jtakw]

Hi,

This should do as you described.

You can also use cell references for the days to add/subtract:

Book3.xlsx
	B	C	D	E	F	G
1	Date	Added	Subtracted		Days to add	Days to subtract
2	12/31/2021	2/7/2022	8/30/2021		35	120
3	12/29/2021	1/31/2022	8/30/2021
4	12/15/2021	1/17/2022	8/16/2021
5	12/31/2021	2/7/2022	8/30/2021
6	12/29/2021	1/31/2022	8/30/2021
7	12/15/2021	1/17/2022	8/16/2021
Sheet1039

Cell Formulas
Range	Formula
C2:C4	C2	=WORKDAY(B2+35,1)-WEEKDAY(WORKDAY(B2+35,1),3)
D2:D4	D2	=WORKDAY(B2-120,1)-WEEKDAY(WORKDAY(B2-120,1),3)
C5:C7	C5	=WORKDAY(B5+F$2,1)-WEEKDAY(WORKDAY(B5+F$2,1),3)
D5:D7	D5	=WORKDAY(B5-G$2,1)-WEEKDAY(WORKDAY(B5-G$2,1),3)

 

[JamesCanale]

Different way for your amusement.

MrExcelPlayground7.xlsx
	A	B
1	12/31/2021
2	1	Monday, January 3, 2022
3	2	Monday, January 3, 2022
4	3	Monday, January 3, 2022
5	4	Monday, January 3, 2022
6	5	Monday, January 3, 2022
7	6	Monday, January 10, 2022
8	7	Monday, January 10, 2022
9	8	Monday, January 10, 2022
10	10	Monday, January 10, 2022
11	20	Monday, January 24, 2022
12	30	Monday, February 7, 2022
13	60	Monday, March 21, 2022
14	90	Monday, May 2, 2022
Sheet13

Cell Formulas
Range	Formula
B2:B14	B2	=INT(WORKDAY($A$1,A2)/7)*7+2

 

[Pumpk1n]

Different way for your amusement.

MrExcelPlayground7.xlsx
	A	B
1	12/31/2021
2	1	Monday, January 3, 2022
3	2	Monday, January 3, 2022
4	3	Monday, January 3, 2022
5	4	Monday, January 3, 2022
6	5	Monday, January 3, 2022
7	6	Monday, January 10, 2022
8	7	Monday, January 10, 2022
9	8	Monday, January 10, 2022
10	10	Monday, January 10, 2022
11	20	Monday, January 24, 2022
12	30	Monday, February 7, 2022
13	60	Monday, March 21, 2022
14	90	Monday, May 2, 2022
Sheet13

Cell Formulas
Range	Formula
B2:B14	B2	=INT(WORKDAY($A$1,A2)/7)*7+2

Thank you! I liked the int formula!

Can you help me with the end date using the same type of formula? For example, if the start date is 1/30/23, I'd like the formula to return a Friday. The number of days is in M3.

 

[Pumpk1n]

Different way for your amusement.

MrExcelPlayground7.xlsx
	A	B
1	12/31/2021
2	1	Monday, January 3, 2022
3	2	Monday, January 3, 2022
4	3	Monday, January 3, 2022
5	4	Monday, January 3, 2022
6	5	Monday, January 3, 2022
7	6	Monday, January 10, 2022
8	7	Monday, January 10, 2022
9	8	Monday, January 10, 2022
10	10	Monday, January 10, 2022
11	20	Monday, January 24, 2022
12	30	Monday, February 7, 2022
13	60	Monday, March 21, 2022
14	90	Monday, May 2, 2022
Sheet13

Cell Formulas
Range	Formula
B2:B14	B2	=INT(WORKDAY($A$1,A2)/7)*7+2

An additional follow up question. If the end date is 12/31/22 and I subtract 84 days, it returns 9/5/22. The difference between the 9/5/22 and 12/31/22 is 116 days. Thanks in advance for helping me understand how this is date is being calculated.

 

[Joe4]

If the end date is 12/31/22 and I subtract 84 days, it returns 9/5/22. The difference between the 9/5/22 and 12/31/22 is 116 days.

You are using WORKDAY in your formula, and that doesn't count weekends!
So that is to be expected, right?

 

[Pumpk1n]

You are using WORKDAY in your formula, and that doesn't count weekends!
So that is to be expected, right?

Got it. Yup.