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:
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