Weeks of Month Dates

[Papi]

What is posted below is not exactly what I need. What I need to do is enter the first Monday of a given month in Cell B3 and have B4 return the first Monday of the next month and the same for the next ten cells B5:B14.

On a similar note, how would a formula go to enter say September 16, 20011 and each cell after (B4:B14) to return the 16th of the following month but return the day on the Monday (Tuesday if using a Holiday range) if the next month falls on a weekend.

And if I could be so bold as to ask how the last one might work if a person needs to return a Friday instead of Monday and move to Thursday if there is a Holiday on Friday.

Any help would be great.

Book1
	B	C	D	E
2	Date
3	10/1/2011
4	10/3/2011Mon
Sheet1

 

[Jerry Sullivan]

Hi Alan,

See the formulas in the screenshot below.

A couple of notes:

1. Since you're entering a first Monday into Cell B3, the formula takes a shortcut. The first Monday of the next month will always be either B3+28 or B3+35. So the same formula would work for first Tuesday, Wednesday....

2. The formula in Col C uses the EOMONTH function. In versions prior to xl2007, the Analysis Toolpak must be installed to use this function.

3. For your questions regarding the 16th of the month, their aren't any holidays coming up on those days, so for my example I had to award us each a National Holiday next year.

Excel Workbook
	B	C	D	E	F	G	H
2	First Monday	16th of Month	16th or Next Workday	16th or Previous Workday		Holidays
3	10/3/2011 Mon	11/16/2011 Wed	11/16/2011 Wed	11/16/2011 Wed		Canada Day	7/1/2011 Fri
4	11/7/2011 Mon	12/16/2011 Fri	12/16/2011 Fri	12/16/2011 Fri		Civic Holiday	8/1/2011 Mon
5	12/5/2011 Mon	1/16/2012 Mon	1/17/2012 Tue	1/13/2012 Fri		New Year's Day	1/1/2012 Sun
6	1/2/2012 Mon	2/16/2012 Thu	2/16/2012 Thu	2/16/2012 Thu		Family Day	2/20/2012 Mon
7	2/6/2012 Mon	3/16/2012 Fri	3/19/2012 Mon	3/15/2012 Thu		Papi's Day	1/16/2012 Mon
8	3/5/2012 Mon	4/16/2012 Mon	4/16/2012 Mon	4/16/2012 Mon		JS411's Day	3/16/2012 Fri
9	4/2/2012 Mon	5/16/2012 Wed	5/16/2012 Wed	5/16/2012 Wed		Good Friday	4/6/2012 Fri
10	5/7/2012 Mon	6/16/2012 Sat	6/18/2012 Mon	6/15/2012 Fri		Easter Monday	4/9/2012 Mon
11	6/4/2012 Mon	7/16/2012 Mon	7/16/2012 Mon	7/16/2012 Mon		Victoria Day	5/21/2012 Mon
12	7/2/2012 Mon	8/16/2012 Thu	8/16/2012 Thu	8/16/2012 Thu		Canada Day	7/2/2012 Mon
13	8/6/2012 Mon	9/16/2012 Sun	9/17/2012 Mon	9/14/2012 Fri		Civic Holiday	8/6/2012 Mon
14	9/3/2012 Mon	10/16/2012 Tue	10/16/2012 Tue	10/16/2012 Tue
Sheet1

 

[venkat1926]

there are many methods see this webpage and apply to suity you

http://www.mrexcel.com/forum/showthread.php?t=19107

for example the first of the month next few months are in the row no. 1
see formulas in row 2(I used Aladin Aktyrek's formula (below)

type the formula in A2 and copy it across

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	1-Oct-11	1-Nov-11	1-Dec-11	1-Jan-12	1-Feb-12	1-Mar-12	1-Apr-12	1-May-12	1-Jun-12	1-Jul-12	1-Aug-12
2	3-Oct-11	7-Nov-11	5-Dec-11	2-Jan-12	6-Feb-12	5-Mar-12	2-Apr-12	7-May-12	4-Jun-12	2-Jul-12	6-Aug-12
Sheet1

 

[Papi]

Hello Jerry and venkat1926,

I was going through Jerry's list and did not notice venkat1926's until I was almost done and then realized my wording gave a somewhat misrepresetation of what I need. B4 looks at B3 and should copy down to B14. C4 looks at C3 and copies down to C14, D4 looks at D3 and copies down to D14 etc. Other than my mistake Jerry, yours was right on the money as you followed what I wrote, which was wrong on my part. I've been trying to re-figure out how to do it but have not accomplished this yet.

Also, I found one more that really throws me off and that is something that happens on the 15th and Last day of the month but moves to the previous workday by exlcuding weekends and Holidays.

 

[barry houdini]

Also, I found one more that really throws me off and that is something that happens on the 15th and Last day of the month but moves to the previous workday by exlcuding weekends and Holidays.

Hello Alan,

With holidays in H3:H13 as per Jerry's suggestion then enter the first date of the sequence in F3 (either 15th of month or last of month) and then put this formula in F4

=WORKDAY(EOMONTH(F3,0)+15*(DAY(F3)>15)+1,-1,H$3:H$13)

format in required date format and copy down

 

[Jerry Sullivan]

C4 looks at C3 and copies down to C14, D4 looks at D3 and copies down to D14 etc. Other than my mistake Jerry, yours was right on the money as you followed what I wrote, which was wrong on my part. I've been trying to re-figure out how to do it but have not accomplished this yet.

Alan,

I just broke out Columns C:E to make it easier to see the dates that are changed by weekends or holidays. You can combine those as shown below and copy the formulas down from C4 and D4.

Excel Workbook
	B	C	D
2	First Monday	16th or Next Workday	16th or Previous Workday
3	10/3/2011 Mon	11/16/2011 Wed	11/16/2011 Wed
4	11/7/2011 Mon	12/16/2011 Fri	12/16/2011 Fri
5	12/5/2011 Mon	1/17/2012 Tue	1/13/2012 Fri
6	1/2/2012 Mon	2/16/2012 Thu	2/16/2012 Thu
7	2/6/2012 Mon	3/19/2012 Mon	3/15/2012 Thu
8	3/5/2012 Mon	4/16/2012 Mon	4/16/2012 Mon
9	4/2/2012 Mon	5/16/2012 Wed	5/16/2012 Wed
10	5/7/2012 Mon	6/18/2012 Mon	6/15/2012 Fri
11	6/4/2012 Mon	7/16/2012 Mon	7/16/2012 Mon
12	7/2/2012 Mon	8/16/2012 Thu	8/16/2012 Thu
13	8/6/2012 Mon	9/17/2012 Mon	9/14/2012 Fri
14	9/3/2012 Mon	10/16/2012 Tue	10/16/2012 Tue
Sheet1

Hopefully, Barry's reply answers your other question - I didn't completely understand what you described about the 15th of the month.

 

[Papi]

The mix-up was on my side on how I worded myself in the initial post. Everything is perfect. It always amazes me the professionalism combined with patience found on the MrExcel website. We are darned lucky to have so many awesome individuals from around the world willing to assist people of all levels, from beginners to experts. I would have to say this is one of the most amazing websites on this earth given the dedication to helping those folks needing assistance and understanding on so many complex issues.

Jerry, you have such a keen ability to read and understand what the user is looking for and given some of the quirky explanations we submit, you have the ability to sum up answers and typically nail them down rather quickly.

Barry, what can be said other than you are one of the masters of the arena and given the number of assists which you handle in a year keeps you on top of the board along with some very strong contenders.

venkat1926, have helped by giving direction that actually helped me resolve issues that I was struggling with.

Many thanks to all of you. I cannot thank you enough.

Jerry, just to indicate my last question, the issue was events that happen typically on the 15th and the last day of each month but should never fall on a weekend and therefore moves the date to the previous workday, so needs to exclude weekends and Holidays.

Alan