Quarter Start and End date for a Broadcast Calendar

[pavan5]

I work in entertainment and media field and here there is something called Broad Cast Calendar which is very different from ISO standard calendar.
Here they make changes to calendar such that every month has almost same number of weeks and every week starts with a Monday and ends on Sunday.


I am trying to find out Quarter start and end date given any date in A2 column.


In broad cast calendar the quarter starts on 1st of October, if 1st of October falls on a weekend, the prior monday is picked as a First day of the quarter


Week start is always monday and end is sunday


for example:
October 1 2016 fall on Saturday, so the quarter for 2017 starts on September 26th 2016 and ends on September 24th 2017
October 1 2017 falls on Sunday, so the quarter for 2018 starts on September 25th 2017 and ends on September 30 2018.


can we put a formula for this?

 

[pavan5]

Using Tetra's formulas (he was right, mine weren't quite right)

Enter a year in a2
I made formulas in A4 and B4 to create December 1st of previous year, and September 1st of that year.
Because we KNOW December 1st is within the first quarter, and september 1st is in the last quarter, regardless if they are monday or not.

I then used Tetra's formulas to get the first day of first quarter, and last day of last quarter. = Year.

Unknown

	A	B
Year
Known day of 1st quarter	Known day of 4th quarter
1st day of 1st quarter	last day of 4th quarter

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Thursday, December 01, 2016[/TD]
[TD="align: right"]Friday, September 01, 2017[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Monday, September 26, 2016[/TD]
[TD="align: right"]Sunday, September 24, 2017[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=DATE(A2-1,12,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=DATE(A2,9,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A7[/TH]
[TD="align: left"]=WORKDAY.INTL(DATE(YEAR(A4+7-WEEKDAY(A4,2)),CEILING(MONTH(A4+7-WEEKDAY(A4,2)),3)-2,2),-1,"0111111")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=WORKDAY.INTL(DATE(YEAR(B4+7-WEEKDAY(B4,2)),CEILING(MONTH(B4+7-WEEKDAY(B4,2)),3)+1,1),-1,"1111110")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thanks Jonmo1 !

this is not giving me the year

I'm trying to find the year for example Sept 25th 2016 (because Oct 1 falls on Saturday) - Sept 30 2017 should give me 2017 as year

 

[Jonmo1]

Now you've lost me....How do you get Sept 30th 2017 ?

for year 2017,
Is the 1st quarter of 2017 NOT Oct/Nov/Dec of 2016 ?
So normally Oct 1st 2016 would be the first day of Q1 2017.
But since that is a Saturday, you want to go back to the previous monday.
That would be Sep 26th 2016
So therefore Sep 26th 2016 is the first day of your Broadcast year 2017.

Is that not right??

 

[pavan5]

Now you've lost me....How do you get Sept 30th 2017 ?

for year 2017,
Is the 1st quarter of 2017 NOT Oct/Nov/Dec of 2016 ?
So normally Oct 1st 2016 would be the first day of Q1 2017.
But since that is a Saturday, you want to go back to the previous monday.
That would be Sep 26th 2016
So therefore Sep 26th 2016 is the first day of your Broadcast year 2017.

Is that not right??

yes you are right !!

for broadcast year 2017, Sept 26th 2016 becomes the 1st day and end of the year becomes Sep 24th 2017

 

[Tetra201]

@pavan5:

If you need to know Broadcast year for any given date, try this formula:

=YEAR(A2)+(MOD(CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3),12)/3=0)

 

[pavan5]

@pavan5:

If you need to know Broadcast year for any given date, try this formula:

=YEAR(A2)+(MOD(CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3),12)/3=0)

thank you !!!! perfect

could you help me finding the week and month number for the broadcast year, please !!

 

[Jonmo1]

oh, I see now.

I thought you wanted the first and last day of the broadcast year. (first day of first quarter and last day of last quarter).

 

[pavan5]

oh, I see now.

I thought you wanted the first and last day of the broadcast year. (first day of first quarter and last day of last quarter).

Sorry Jonmo1 if I could not explain properly , my apologies

 

[Jonmo1]

No worries, glad you got it sorted.

 

[Tetra201]

@pavan5:

If you need to know Broadcast year month for any given date, try this formula:

=MOD(MONTH(A2+7-WEEKDAY(A2,2))+2,12)+1

 

[pavan5]

@pavan5:

If you need to know Broadcast year month for any given date, try this formula:

=MOD(MONTH(A2+7-WEEKDAY(A2,2))+2,12)+1

perfect !!! thanks mate

how do I get week number of the year and month number of the quarter ??

this broad cast calendar is so odd, i could get all this for normal calendar with ready made formulas..