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]

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)

not working W1 for Q1 of 2017 should start from Sept 26 2016 to Oct 2nd 2016

 

[Jonmo1]

I thought it was just the week number relative to the quarter, not the whole year...

You then need B2 to be the starting date of Q1 for that year.

 

[pavan5]

I thought it was just the week number relative to the quarter, not the whole year...

You then need B2 to be the starting date of Q1 for that year.

how can we do it for the year ?

 

[Jonmo1]

Put in a date that you know will be in the first quarter of the year, say December 1st.
Use the previous formula that gives the 1st day of the quarter for that date.
Use that cell in place of B2 in this formula
=CEILING((A2-B2+1)/7,1)

 

[pavan5]

Put in a date that you know will be in the first quarter of the year, say December 1st.
Use the previous formula that gives the 1st day of the quarter for that date.
Use that cell in place of B2 in this formula
=CEILING((A2-B2+1)/7,1)

when I do this the excel is considering the week start day as Sunday , I need the week start day as Monday

 

[Jonmo1]

It's considering whatever day is in B2 as the starting day of the week.

 

[pavan5]

It's considering whatever day is in B2 as the starting day of the week.

how can we calculate week number for the year ?

also how do I calculate the year ?

the start of the broad cast year is from Oct 1st if its not a Monday, it would start from previous month Monday in this case Sept 26th 2016

 

[Jonmo1]

You already have the formulas you need to calculate the year.

You have a formula to find the first day of a quareter based on a given date.
So pick a date that you know will always be in the First Quarter of your year.
Surely December 1st will always be in the first quarter of your year.
So the previous formula given that returns the first day of the quarter for December 1st, therefore is the first day of the year.

You have a formula to find the last day of a quarter based on a given date.
So pick a date that you know will always be in the Last quarter of the year
Surely September 1st will always be in the last quarter of your year
So the previous formula given that returns the last day of the quarter based on September 1st, therefore is the last day of the year.

 

[pavan5]

You already have the formulas you need to calculate the year.

You have a formula to find the first day of a quareter based on a given date.
So pick a date that you know will always be in the First Quarter of your year.
Surely December 1st will always be in the first quarter of your year.
So the previous formula given that returns the first day of the quarter for December 1st, therefore is the first day of the year.

You have a formula to find the last day of a quarter based on a given date.
So pick a date that you know will always be in the Last quarter of the year
Surely September 1st will always be in the last quarter of your year
So the previous formula given that returns the last day of the quarter based on September 1st, therefore is the last day of the year.

Thanks Jonmo1 !! I'm sorry am very very bad at excel, could you help me in getting the formula for this, please.

 

[Jonmo1]

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
1	Year
2	2017
3	Known day of 1st quarter	Known day of 4th quarter
4	Thursday, December 01, 2016	Friday, September 01, 2017
5
6	1st day of 1st quarter	last day of 4th quarter
7	Monday, September 26, 2016	Sunday, September 24, 2017
Sheet1

Cell Formulas
Range	Formula
A4	=DATE(A2-1,12,1)
A7	=WORKDAY.INTL(DATE(YEAR(A4+7-WEEKDAY(A4,2)),CEILING(MONTH(A4+7-WEEKDAY(A4,2)),3)-2,2),-1,"0111111")
B4	=DATE(A2,9,1)
B7	=WORKDAY.INTL(DATE(YEAR(B4+7-WEEKDAY(B4,2)),CEILING(MONTH(B4+7-WEEKDAY(B4,2)),3)+1,1),-1,"1111110")