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?

 

[Jonmo1]

Wouldn't Q1 2017 END date be in December 2016?

 

[pavan5]

Wouldn't Q1 2017 END date be in December 2016?

yes that's right, the end of Q1 would be Dec 25th 2016

 

[pavan5]

yes that's right, the end of Q1 would be Dec 25th 2016

Q1 2017 - Sept 25th 2016 - Dec 25th 2016
Q2 2017 - dec 26th 2016 - Mar 26th 2016
Q3 2017 - mar 27th 2017 - Jun 25th 2017
Q4 2017 - Jun 26th - Sep - Sept 24th 2017

 

[Jonmo1]

Any given date in A2

First day of quarter
=WORKDAY.INTL(DATE(YEAR(A2),(CEILING(MONTH(A2)/3,1)-1)*3+1,2),-1,"0111111")

Last day of quarter
=WORKDAY.INTL(DATE(YEAR(A2),(CEILING(MONTH(A2)/3,1)-1)*3+4,1),-1,"1111110")

 

[Tetra201]

@Jonmo1:

Your formulas from Post #5 stumble over Sep. 26-30, 2016, etc.

Here is my take on this:

First day of quarter
=WORKDAY.INTL(DATE(YEAR(A2+7-WEEKDAY(A2,2)),CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3)-2,2),-1,"0111111")

Last day of quarter
=WORKDAY.INTL(DATE(YEAR(A2+7-WEEKDAY(A2,2)),CEILING(MONTH(A2+7-WEEKDAY(A2,2)),3)+1,1),-1,"1111110")

 

[pavan5]

Any given date in A2

First day of quarter
=WORKDAY.INTL(DATE(YEAR(A2),(CEILING(MONTH(A2)/3,1)-1)*3+1,2),-1,"0111111")

Last day of quarter
=WORKDAY.INTL(DATE(YEAR(A2),(CEILING(MONTH(A2)/3,1)-1)*3+4,1),-1,"1111110")

thank you !

 

[Tetra201]

@pavan5:

If you need to know quarter number for any given date, try this formula:

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

 

[pavan5]

@pavan5:

If you need to know quarter number for any given date, try this formula:

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

Awesome !! thank you , works like a charm ,

Similarly can we find week number for that particular quarter and year ??

 

[Jonmo1]

If you already have a formula giving the quarter start date....

A2 = Any Given Date
B2 = Formula giving Qarter Starting Date based on A2

try
Week#: =CEILING((A2-B2+1)/7,1)