Month Name for 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 month name for any given date in A2 column.


In broad cast calendar the week starts on Monday and 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 and similarly if the 1st of any month falls on a day apart from Monday the prior monday is considered as start date of the month


Week start is always Monday and end is Sunday


for example:
Jul 1 2017 fall on Saturday,so the month start date would be June 26th 2017, so the month name should be June for actual date Jul 01 2017.

Aug 1 2017 fall on Tuesday,so the month start date would be July 31st 2017, so the month name should be July for actual date Aug 01 2017.


can we put a formula for this?

 

[oldbrewer]

[TABLE="width: 526"]
<colgroup><col span="4"><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/12/2016[/TD]
[TD="align: right"]Sun[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/12/2016[/TD]
[TD="align: right"]Mon[/TD]
[TD="colspan: 2"]quarter start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/12/2016[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/12/2016[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/12/2016[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/12/2016[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]Sat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]Sun[/TD]
[TD]weekend[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]Mon[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/01/2017[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/01/2017[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]so does quarter 1 2017 start on 26/12/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and the month is December ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pavan5]

[TABLE="width: 526"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/12/2016[/TD]
[TD="align: right"]Sun[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/12/2016[/TD]
[TD="align: right"]Mon[/TD]
[TD="colspan: 2"]quarter start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/12/2016[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/12/2016[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/12/2016[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/12/2016[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]Sat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]Sun[/TD]
[TD]weekend[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]Mon[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/01/2017[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/01/2017[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]so does quarter 1 2017 start on 26/12/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and the month is December ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That's correct ,, it starts on 26/12/2016 but that for reference, what I'm looking is for month name for a given date like
for example:
Jul 1 2017 fall on Saturday,so the month start date would be June 26th 2017, so the month name should be June for actual date Jul 01 2017.
Aug 1 2017 fall on Tuesday,so the month start date would be July 31st 2017, so the month name should be July for actual date Aug 01 2017.

 

[pavan5]

[TABLE="width: 526"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/12/2016[/TD]
[TD="align: right"]Sun[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/12/2016[/TD]
[TD="align: right"]Mon[/TD]
[TD="colspan: 2"]quarter start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/12/2016[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/12/2016[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/12/2016[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/12/2016[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2016[/TD]
[TD="align: right"]Sat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]Sun[/TD]
[TD]weekend[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]Mon[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]Tue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/01/2017[/TD]
[TD="align: right"]Wed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]Thu[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/01/2017[/TD]
[TD="align: right"]Fri[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]so does quarter 1 2017 start on 26/12/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and the month is December ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That's correct ,, it starts on 26/12/2016 but that for reference, what I'm looking is for month name for a given date like
for example:
Jul 1 2017 fall on Saturday,so the month start date would be June 26th 2017, so the month name should be June for actual date Jul 01 2017.
Aug 1 2017 fall on Tuesday,so the month start date would be July 31st 2017, so the month name should be July for actual date Aug 01 2017.

 

[Tetra201]

Isn't it like this?

July: 6/26/17 - 7/30/17
August: 7/31/17 - 8/27/17
September: 8/28/17 - 9/24/17
October: 9/25/17 - 10/29/17

 

[pavan5]

Isn't it like this?

July: 6/26/17 - 7/30/17
August: 7/31/17 - 8/27/17

September: 8/28/17 - 9/24/17
October: 9/25/17 - 10/29/17

Hi Tetra !
yes that's correct, I want like tat for the given dates

 

[Tetra201]

... yes that's correct, I want like tat for the given dates

Then see if this works for you:

=TEXT(28*(MOD(MONTH(A2+7-WEEKDAY(A2,2))-1,12)+1),"mmmm")

 

[pavan5]

Then see if this works for you:

=TEXT(28*(MOD(MONTH(A2+7-WEEKDAY(A2,2))-1,12)+1),"mmmm")

Awesome !!!!!! works like a charm ..thanks much !!!!

 

[Tetra201]

You are welcome.