Re: Dates Table to classify a given date multiple ways with additional special "Broadcast Calendar" need

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

1. I populated a column with every date between 2020-01-01 and 2046-01-01 (26 years). 
2. 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.
3. I created columns for Broadcast Year, Broadcast Week, Broadcast Day(numbers 1-7, Mon=1), and copied the Actuals into the corresponding Broadcast columns.
4. 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.
5. When a Jan 1 falls on a Monday, you're golden. That's the start of a new Broadcast Year, Week, Day.
6. 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
------------------------------