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..

key	date	start_date	end_date
20170530	2017-05-30	4/30/2017	5/28/2017
20170531	2017-05-31	4/30/2017	5/28/2017
20170601	2017-06-01	5/29/2017	6/25/2017
20170602	2017-06-02	5/29/2017	6/25/2017
20170603	2017-06-03	5/29/2017	6/25/2017
20170604	2017-06-04	5/29/2017	6/25/2017
20170605	2017-06-05	5/29/2017	6/25/2017
20170606	2017-06-06	5/29/2017	6/25/2017
20170607	2017-06-07	5/29/2017	6/25/2017
20170608	2017-06-08	5/29/2017	6/25/2017
20170609	2017-06-09	5/29/2017	6/25/2017
20170610	2017-06-10	5/29/2017	6/25/2017
20170611	2017-06-11	5/29/2017	6/25/2017
20170612	2017-06-12	5/29/2017	6/25/2017
20170613	2017-06-13	5/29/2017	6/25/2017
20170614	2017-06-14	5/29/2017	6/25/2017
20170615	2017-06-15	5/29/2017	6/25/2017
20170616	2017-06-16	5/29/2017	6/25/2017
20170617	2017-06-17	5/29/2017	6/25/2017
20170618	2017-06-18	5/29/2017	6/25/2017
20170619	2017-06-19	5/29/2017	6/25/2017
20170620	2017-06-20	5/29/2017	6/25/2017
20170621	2017-06-21	5/29/2017	6/25/2017
20170622	2017-06-22	5/29/2017	6/25/2017
20170623	2017-06-23	5/29/2017	6/25/2017
20170624	2017-06-24	5/29/2017	6/25/2017
20170625	2017-06-25	5/29/2017	6/25/2017
20170626	2017-06-26	5/29/2017	6/25/2017
20170627	2017-06-27	5/29/2017	6/25/2017
20170628	2017-06-28	5/29/2017	6/25/2017
20170629	2017-06-29	5/29/2017	6/25/2017
20170630	2017-06-30	5/29/2017	6/25/2017
20170701	2017-07-01	6/26/2017	7/30/2017

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[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