start date and end date of the month in broad cast calendar

[pavan5]

Can someone help me in finding out a formula for figuring out first day and last of the month for a broad cast calendar. Example for Jun 1 2017, the start of month should be May 26th 2017 since Jun 1st falls on a Thursday end date would be normal Jun 30 2017, like wise for Jul 2017, the start date of the month would be June 29 2017 for Aug 2017 it would be July 31 2017 ...The first day(1) of any month should be on Monday.

Any help would be greatly appreciated!

 

[Jonmo1]

Anyway, if you have XL2010+ these will work for you..

If the actual calendar 1st day of the month is in A1

The previous monday is
=WORKDAY.INTL(A1+1,-1,"0111111")

And last day of month is
=EOMONTH(A1,0)

 

[pavan5]

So I'll ask again...

If today was June 29th.
Do I call that plain old June 29th, or the 1st day of July ?


Which version of Excel are you using?

it should be like this

date month_start_date_bcast month_end_date_bcast
6/29/2017 05/29/2017 6/25/2017
6/30/2017 05/29/2017 6/25/2017
7/01/2017 06/26/2017 7/30/2017

end date should be the Sunday

 

[Jonmo1]

Ok,

End Date is
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"1111110")

 

[pavan5]

So I'll ask again...

If today was June 29th.
Do I call that plain old June 29th, or the 1st day of July ?


Which version of Excel are you using?

this is what am looking to do ...sample did it manually..
[TABLE="width: 423"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]key [/TD]
[TD]date[/TD]
[TD]start_date[/TD]
[TD]end_date[/TD]
[/TR]
[TR]
[TD="align: right"]20170530[/TD]
[TD="align: right"]2017-05-30[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/28/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170531[/TD]
[TD="align: right"]2017-05-31[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/28/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170601[/TD]
[TD="align: right"]2017-06-01[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170602[/TD]
[TD="align: right"]2017-06-02[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170603[/TD]
[TD="align: right"]2017-06-03[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170604[/TD]
[TD="align: right"]2017-06-04[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170605[/TD]
[TD="align: right"]2017-06-05[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170606[/TD]
[TD="align: right"]2017-06-06[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170607[/TD]
[TD="align: right"]2017-06-07[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170608[/TD]
[TD="align: right"]2017-06-08[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170609[/TD]
[TD="align: right"]2017-06-09[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170610[/TD]
[TD="align: right"]2017-06-10[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170611[/TD]
[TD="align: right"]2017-06-11[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170612[/TD]
[TD="align: right"]2017-06-12[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170613[/TD]
[TD="align: right"]2017-06-13[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170614[/TD]
[TD="align: right"]2017-06-14[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170615[/TD]
[TD="align: right"]2017-06-15[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170616[/TD]
[TD="align: right"]2017-06-16[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170617[/TD]
[TD="align: right"]2017-06-17[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170618[/TD]
[TD="align: right"]2017-06-18[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170619[/TD]
[TD="align: right"]2017-06-19[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170620[/TD]
[TD="align: right"]2017-06-20[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170621[/TD]
[TD="align: right"]2017-06-21[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170622[/TD]
[TD="align: right"]2017-06-22[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170623[/TD]
[TD="align: right"]2017-06-23[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170624[/TD]
[TD="align: right"]2017-06-24[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170625[/TD]
[TD="align: right"]2017-06-25[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170626[/TD]
[TD="align: right"]2017-06-26[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170627[/TD]
[TD="align: right"]2017-06-27[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170628[/TD]
[TD="align: right"]2017-06-28[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170629[/TD]
[TD="align: right"]2017-06-29[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170630[/TD]
[TD="align: right"]2017-06-30[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]6/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20170701[/TD]
[TD="align: right"]2017-07-01[/TD]
[TD="align: right"]6/26/2017[/TD]
[TD="align: right"]7/30/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

So your given date to start with is not necessarily the first of the month.

With ANY date in A1

First day of calendar month from date in A1
=WORKDAY.INTL(A1-DAY(A1)+2,-1,"0111111")

Last day of calendar month from date in A1
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"1111110")

 

[pavan5]

my start date for the entire data is 1/1/1900 from there, series of dates until 12/31/2029

 

[pavan5]

you are awesome !!!! thanks a ton !!!!!!!

 

[pavan5]

So your given date to start with is not necessarily the first of the month.

With ANY date in A1

First day of calendar month from date in A1
=WORKDAY.INTL(A1-DAY(A1)+2,-1,"0111111")

Last day of calendar month from date in A1
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"1111110")

you are awesome !!!! thanks a ton !!!!!!!

am a newbie to this site, so pardon my duplication of replies

 

[Jonmo1]

You're welcome.

 

[pavan5]

You're welcome.

sorry to ask but can this logic be used to find the quarter start and end date , the broad cast calendar quarter starts in October but the dates logic remains same