52/53 week year end date calculator
We want to know if there is an Excel formula that serves as a 52/53 week year end date calculator for all situations. Such a formula would answer a question of this nature:
• For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the last Saturday in December or
• For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the closest Sunday in January
The required input values would include:
• Year end day of week, e.g., Saturday
• Closest day to calendar month end or last day in month
We work with excel enough to be handy with using VBA to automate tasks like this, so we are open to a solution that would involve us plugging code into a VBA module.
Will award full points for a useful solution.