Help with Formula and Days

[Pumpk1n]

Hello. I have a formula which works great for 2022 to give me the monday start and friday end dates based on 12/31/22. Right now if I change the date to 12/31/23, the start dates are all Tuesday. I'd like to be able to change the 12/31 date each year and automatically have all the start dates be a Monday. How do I accomplish that?

Fixed cell: B1
Current Value in B1: 12/31/22
Example:

• G5: user enters 9 weeks to start their task on Monday, 10/31/22 which is 9 weeks before 12/31
• H5: formula for number of days (9 weeks *7 days)
• I5: user enters 6 weeks for the duration to end their task on a Friday, 12/9/22
• J5: Monday Start Date formula: =INT($B$1-$H3)/7*7+2
• K5: Friday Due Date formula: =INT($J5+($I5-1)*7)+4

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B					G	H	I	J	K
1		31/12/2022
2
3
4
5							9	63	6	31/10/2022	09/12/2022
Data

Cell Formulas
Range	Formula
H5	H5	=G5*7
J5	J5	=WORKDAY.INTL($B$1,-G5,"0111111")
K5	K5	=WORKDAY.INTL(J5,I5,"1111011")

 

[Pumpk1n]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B					G	H	I	J	K
1		31/12/2022
2
3
4
5							9	63	6	31/10/2022	09/12/2022
Data

Cell Formulas
Range	Formula
H5	H5	=G5*7
J5	J5	=WORKDAY.INTL($B$1,-G5,"0111111")
K5	K5	=WORKDAY.INTL(J5,I5,"1111011")

Thanks for the tip on updating my profile. I'm on Windows - Office 365. Your formula worked perfectly! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.
Thanks also for updating your profile.