I was able to accomplish this in Google Sheets. Not fully automated or formula-driven, but enough to where I can accomplish and move on for now. If anyone is interested, this is what I did...
- I populated a column with every date between 2020-01-01 and 2046-01-01 (26 years).
- I separated the actual Year, Month, Week, Day(date), Day(weekday M-F) into separate columns. Most of these equate to the same Year-Week-Day(1-7) of the broadcast calendar.
- I created columns for Broadcast Year, Broadcast Week, Broadcast Day(numbers 1-7, Mon=1), and copied the Actuals into the corresponding Broadcast columns.
- I made a column to flag all the Jan 1's, so I could easily jump between those rows as I move from year to year.
- When a Jan 1 falls on a Monday, you're golden. That's the start of a new Broadcast Year, Week, Day.
- When a Jan 1 was anything other than Monday, I jumped up a few rows back to the immediately preceding Monday and manually changed the Broadcast Year and Broadcast Week values where necessary.
I would've liked to had figured out how to do it all with formulas, but I used just enough simple formulas to make this doable in a short amount of time, so I'll consider it a win. From here I will simply import the corresponding values ​into a QB table and move on. I hope this helps someone in the future. Cheers!
#broadcast #Calendar #dates​
------------------------------
Mike Freyder
------------------------------
