Year for Broadcast Calendar

[Cadams2021]

Hi, I work for a TV media buying agency and we work off of a broadcast calendar It is VERY different than a standard calendar. (see attached).

We need to find the Broadcast QTR, Month & Year for any given date very regularly.
I was able to find formals to calculate the Broadcast QTR & Broadcast Month for any given date by using the following:

QTR: ="Q"&MOD(CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3),13)/3
Month =TEXT(28*(MOD(MONTH(A2+7-WEEKDAY(A2,2))-1,12)+1),"mmmm")

Is there a formula to figure out the year?

 

[Cadams2021]

I really only have an issue with the 1st week of the broadcast year, as it starts in December not January

 

[Dave Patton]

How do you define a year?
Do you have a 52/53 week year?

When does the current year end?

 

[Cadams2021]

How do you define a year?
Do you have a 52/53 week year?

When does the current year end?

some year are 52 weeks & some are 53.

2017 = 53 weeks
2018 = 52 weeks
2019 = 52 weeks
2020 = 52 weeks
2021 = 52 weeks
2022 = 52 weeks
2023 = 53 weeks
2024 = 52 weeks

 

[Cadams2021]

some year are 52 weeks & some are 53.

2017 = 53 weeks
2018 = 52 weeks
2019 = 52 weeks
2020 = 52 weeks
2021 = 52 weeks
2022 = 52 weeks
2023 = 53 weeks
2024 = 52 weeks

some year are 52 weeks & some are 53.

2017 = 53 weeks
2018 = 52 weeks
2019 = 52 weeks
2020 = 52 weeks
2021 = 52 weeks
2022 = 52 weeks
2023 = 53 weeks
2024 = 52 weeks

2022 = 12/2/21 - 12/25/22
2023 = 12/26/22 - 12/31/23

 

[Cadams2021]

How do you define a year?
Do you have a 52/53 week year?

When does the current year end?

2022 = 12/2/21 - 12/25/22 52 weeks
2023 = 12/26/22 - 12/31/23 53 weeks

 

[Cadams2021]

2022 = 12/2/21 - 12/25/22 52 weeks
2023 = 12/26/22 - 12/31/23 53 weeks

How do you define a year?
Do you have a 52/53 week year?

When does the current year end?

sorry im new to this. I made a mistake.. 12/27/21 is the start of the year for 2022, no 12/2/22
2022 = 12/27/21 - 12/25/22 52 weeks
2023 = 12/26/22 - 12/31/23 53 weeks

 

[Dave Patton]

Please check the the start and end dates. The calendar starts on Dec 26, 2022.
Try either of the formulas, The first requires the lookup table.
Do you want Excel to prepare a calendar for the 52 or 53 weeks on 1 sheet?

T202303a.xlsm
	A	B	C	D	E	F	G	H
1
2					Start	Year	Weeks	End
3	5-Jan-23	2023	2023					Sun 25-Dec-22
4	1-Jan-24	2024	2024		Mon 26-Dec-22	2023	53	Sun 31-Dec-23
5	28-Dec-22	2023	2023		Mon 01-Jan-24	2024	52	Sun 29-Dec-24
6
7
2e

Cell Formulas
Range	Formula
B3:B5	B3	=LOOKUP(A3,$E$4:$F$5)
C3:C5	C3	=LOOKUP(A3,{44921,2023;45292,2024})
E4:E5	E4	=H3+1
H4:H5	H4	=H3+G4*7

 

[Tetra201]

... Is there a formula to figure out the year?

See if the following formula works for you:

=YEAR(WORKDAY.INTL(A2-1,1,"1111110"))

 

[Cadams2021]

Please check the the start and end dates. The calendar starts on Dec 26, 2022.
Try either of the formulas, The first requires the lookup table.
Do you want Excel to prepare a calendar for the 52 or 53 weeks on 1 sheet?

T202303a.xlsm
	A	B	C	D	E	F	G	H
1
2					Start	Year	Weeks	End
3	5-Jan-23	2023	2023					Sun 25-Dec-22
4	1-Jan-24	2024	2024		Mon 26-Dec-22	2023	53	Sun 31-Dec-23
5	28-Dec-22	2023	2023		Mon 01-Jan-24	2024	52	Sun 29-Dec-24
6
7
2e

Cell Formulas
Range	Formula
B3:B5	B3	=LOOKUP(A3,$E$4:$F$5)
C3:C5	C3	=LOOKUP(A3,{44921,2023;45292,2024})
E4:E5	E4	=H3+1
H4:H5	H4	=H3+G4*7

See if the following formula works for you:

=YEAR(WORKDAY.INTL(A2-1,1,"1111110"))

It works for some years but not all of them. I think it is because some weeks have 52 weeks & some have 53 weeks in the year