Help with Formula and Days

Pumpk1n

Board Regular
Joined
Jan 31, 2017
Messages
86
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,713
Office Version
  1. 365
Platform
  1. Windows
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
ABGHIJK
131/12/2022
2
3
4
5963631/10/202209/12/2022
Data
Cell Formulas
RangeFormula
H5H5=G5*7
J5J5=WORKDAY.INTL($B$1,-G5,"0111111")
K5K5=WORKDAY.INTL(J5,I5,"1111011")
 
Solution

Pumpk1n

Board Regular
Joined
Jan 31, 2017
Messages
86
Office Version
  1. 365
Platform
  1. Windows
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
ABGHIJK
131/12/2022
2
3
4
5963631/10/202209/12/2022
Data
Cell Formulas
RangeFormula
H5H5=G5*7
J5J5=WORKDAY.INTL($B$1,-G5,"0111111")
K5K5=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,713
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
Thanks also for updating your profile. (y)
 

Forum statistics

Threads
1,176,123
Messages
5,901,515
Members
434,899
Latest member
powerappsjoker99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top