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

Question

Hello everyone! New user, first post. I'm looking for help in building a table where each record will represent a unique date and additional fields will equate each of those dates to a Quarter, Fiscal Year, Actual Year, etc... I think I can get most of that done on my own, however, I'd like a field to equate that specific date to a week of a Broadcast Calendar. For anyone not familiar, the Broadcast Calendar works like this (via wikipedia, main points below),

Every week in the broadcast calendar starts on a Monday and ends on a Sunday.
The key link between the broadcast and Gregorian calendars is that the first week of every broadcast month always contains the Gregorian calendar first of the month.&nbsp;For example, if January 1 falls on a Saturday, then the broadcast calendar year would begin on the preceding Monday, December 27.
Days and weeks in a broadcast calendar are often referred to by number rather than name, as in Week 47, Day 3.

I'd like to create a formula field that equates the day to the broadcast calendar equivalent of Year 2020, Week 47, Day3.This is not going to make or break my app and I'm in no rush, I just thought this was an interesting use-case that I should learn from. Thanks in advance for any help you can provide.--------------------------------Mike Freyder------------------------------

mikefreyder · Answer

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).&nbsp;
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&nbsp;#Calendar&nbsp;#dates​------------------------------Mike Freyder------------------------------

Forum Discussion

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