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

P

pavan5

Board Regular
Joined
Jun 13, 2017
Messages
56
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!
 
Sort by date Sort by votes
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)
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Jonmo1 said:
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?
Click to expand...


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
 
Upvote 0
Ok,

End Date is
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"1111110")
 
Last edited:
Upvote 0
Jonmo1 said:
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?
Click to expand...

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]
 
Upvote 0
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")
 
Upvote 0
Jonmo1 said:
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")
Click to expand...

you are awesome !!!! :cool: thanks a ton !!!!!!!

am a newbie to this site, so pardon my duplication of replies
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
calculate monthly value
Replies
0
Views
237
BrutalLogiC
B
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
476
alansidman
A
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
912
Legacy 352679
L

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top