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]

perfect !!! thanks mate

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

 

[Tetra201]

For week number of the year, try

=INT((A2-WORKDAY.INTL(DATE(YEAR(A2)+(MOD(CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3),12)/3=0)-1,10,2),-1,"0111111"))/7)+1

For month number of the quarter, try

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

 

[pavan5]

For week number of the year, try

=INT((A2-WORKDAY.INTL(DATE(YEAR(A2)+(MOD(CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3),12)/3=0)-1,10,2),-1,"0111111"))/7)+1

For month number of the quarter, try

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

Its giving only 3 quarters , not getting the 4th quarter

 

[Tetra201]

Its giving only 3 quarters , not getting the 4th quarter

Example?

 

[pavan5]

Example?

sorry my bad, its working fine, its just the calendar is confusing ....thanks a ton !!! u guys are awesome with excel formulas....

 

[pavan5]

Example?

Hi Tetra !

can we help me find week of the year , month of the year and nth day of the year for a fiscal year tat starts from Oct 1 and ends on Sept 30th. for a normal fiscal calendar not broad cast calendar