How to Identify the End of the 4th Week of a Month ?? - 2

[monirg]

<HR>
Note:
This is a re-post of my earlier Thread by the same Title (posted: Fri, July 08, 2011, ~ 3:30 PM EST.)
My Original Post and ALL replies had vanished from the Forum. I've notified the Admin, and can assure you it was NOT my fault!
I was about to try both the macro procedure and the w/s procedure, when I was advised that the Thread had been killed off by mistake by a rookie moderator on the Forum!
I'm doing my best here to remember the words and phrases I posted earlier, and I'd appreciate re-posting your earlier reply; if you still have it.
Thanks a lot.


<HR>

Hello;

My question may sound trivial to some, but it isn't really.

1) I need to identify the date of Friday of the 4th Week of a given month of a given year.
For example:
.... it is 23 for September, 2011
.... and it is 22 for October 2012.

2) I thought of creating a full 12-month calendar for each year of interest, and manually highlighting the date of Friday of the 4th week of each month.
I immediately realized that it would be messy and susceptible to errors!

3) I suppose one can create a smart macro to do just that, but more neatly:
... accepting a valid name for the month (i.e.; January to December)
... accepting a valid year (say; 2011 to 2021)
... and returning the date, something like:
..... "End of 4th Week: Friday, 28 September, 2012"

4) Alternatively, a w/s procedure using the Date functions, and no macros, may also do the trick:
... select the month & year from drop-down lists (say, starting C10 & D10 Sheet1)
... (month & year data validation ranges compiled on Sheet2)
... and the Date formula returns in cell C5 Sheet1 the date; something like:
..... "End of 4th Week: Friday, 26 October, 2012"

5) What if the above identified Friday is a Bank Holiday ??
... (may use a bogus holiday list for now)
... The w/s Date formula should instead return something like:
..... "End of 4th Week: Thursday, 25 October, 2012 (Friday is a Bank Holiday)"
... Does this add an unnecessary complication at this stage, and I should worry about it later ??

Can someone please provide some guidance on how-to, or provide a link to relevant macro / worksheet / Date formula, if already exist.

Thank you kindly.

 

[barry houdini]

...those pesky rookie moderators.....

I posted something along these lines yesterday:

If you have month in text (e.g. July) in A2 and year in B2 then this formula in C2 will give you the 4th Friday in that month

=(1&A2&B2)-WEEKDAY(2&A2&B2)+28

If you want it to look exactly as you specified then custom format the cell as

"End of 4th Week: "dddd, d mmmm, yyyy

Going one step further to take into account your holiday requirement you could use this formula

=TEXT((1&A2&B2)-WEEKDAY(2&A2&B2)+28,"""End of 4th Week: ""dddd, d mmmm, yyyy")&IF(COUNTIF(H$2:H$10,(1&A2&B2)-WEEKDAY(2&A2&B2)+28)," (Friday is a Bank Holiday)","")

assuming you have a list of holidays in H2:H10

 

[Rick Rothstein]

Here is a generic formula for calculating the so-and-so date for the nth day of the month...

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where Yr is the year number, MM is the month number, Nth is the occurrence number (that is, 1 for 1st, 2 for 2nd, 3 for 3rd etc.) and where DoW stands for day of the week where you would use 1 for Sunday, 2 for Monday, etc. You should be able to adapt this to your current setup.

 

[monirg]

Hi Barry;

Thank you for taking the time to re-post your earlier reply. Greatly appreciated.

a) Your formula works fine, but doesn't account for the a non-empty holiday list.
The difficulty is the date format to use in H2:H10.

b) Let us say, the Bank Holidays for 2011 are:
28 January, 2011
24 June, 2011
23 December, 2011

c) How do I enter the above 3 dates in H2 and down ??
Entering the holiday dates in the above format doesn't change the return from the formula.

Thank you.

 

[monirg]

Hi Rick;

Thank you for your reply.

1) If the month list is: January, February, March, April, ..., December
and the year list is: 2010, 2011, 2012, 2013...., 2020.
Is 2012 year number 3 in your formula ??

2) For a correct interpretation of your formula:

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

could you please rewrite it to identify the end (Friday) of the 4th week of August 2013 ??

Thank you.

 

[Rick Rothstein]

2) For a correct interpretation of your formula:
=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))
could you please rewrite it to identify the end (Friday) of the 4th week of August 2013 ??

Dow = 6 (where Sunday is 1, Monday is 2, etc.)
Yr = 2013 (use the 4-digit year)
MM = 8 (August is the 8th month)
Nth = 4 (4th week)

Substituting into formula yields...

=DATE(2013,8,1+7*4)-WEEKDAY(DATE(2013,8,8-4))

and, since we have only constants, performing the indicated math yields...

=DATE(2013,8,29)-WEEKDAY(DATE(2013,8,4))

as the final formula. Of course, you will probably either have a full date in a cell (from which you can extract the year and month using the YEAR and MONTH function) or you have them in separate cells in which case you can substitute the cell references directly.

By the way, if you actually have the month names in your cell rather than the month number, you can convert the text name to numerical value using this function call...

MONTH(1&A1&2000)

where I have assumed A1 contains the month name.

Does that help?

 

[barry houdini]

The difficulty is the date format to use in H2:H10

The format doesn't matter as long as excel recognises them as valid dates - I suggest you enter in the normal date format for your region (is that dd/mm/yy for Canada?) - e.g. for 24th June enter 24/06/11

You can then custom format those cells to display the dates any way you want

 

[barry houdini]

See example below.....

C2 has this formula copied down

=(1&A2&B2)-WEEKDAY(2&A2&B2)+28

D2 has the formula

=TEXT((1&A2&B2)-WEEKDAY(2&A2&B2)+28,"""End of 4th Week: ""dddd, d mmmm, yyyy")&IF(COUNTIF(H$2:H$10,(1&A2&B2)-WEEKDAY(2&A2&B2)+28)," (Friday is a Bank Holiday)","")

Note that the C2 formula is essentially the same as Rick's suggestion - amended to deal with month in text format

4th_Friday.xlsx
	A	B	C	D	E	F	G	H
1	Month	Year	Formula1	Formula2				Holidays
2	January	2011	Endof4thWeek:Friday,28January,2011	Endof4thWeek:Friday,28January,2011(FridayisaBankHoliday)				28-Jan-2011
3	February	2011	Endof4thWeek:Friday,25February,2011	Endof4thWeek:Friday,25February,2011				24-Jun-2011
4	March	2011	Endof4thWeek:Friday,25March,2011	Endof4thWeek:Friday,25March,2011				23-Dec-2011
5	April	2011	Endof4thWeek:Friday,22April,2011	Endof4thWeek:Friday,22April,2011
6	May	2011	Endof4thWeek:Friday,27May,2011	Endof4thWeek:Friday,27May,2011
7	June	2011	Endof4thWeek:Friday,24June,2011	Endof4thWeek:Friday,24June,2011(FridayisaBankHoliday)
8	July	2011	Endof4thWeek:Friday,22July,2011	Endof4thWeek:Friday,22July,2011
9	August	2011	Endof4thWeek:Friday,26August,2011	Endof4thWeek:Friday,26August,2011
10	September	2011	Endof4thWeek:Friday,23September,2011	Endof4thWeek:Friday,23September,2011
11	October	2011	Endof4thWeek:Friday,28October,2011	Endof4thWeek:Friday,28October,2011
12	November	2011	Endof4thWeek:Friday,25November,2011	Endof4thWeek:Friday,25November,2011
13	December	2011	Endof4thWeek:Friday,23December,2011	Endof4thWeek:Friday,23December,2011(FridayisaBankHoliday)
14
sheet1

 

[monirg]

Hi Rick;

Perfect! Thank you very much for your help.
The Bank Holiday requirement (Item 5 of the OP) is addressed by Barry Houndini (see below).

Regards.

 

[monirg]

Hi Barry;

Excellent! It works perfectly.
Will concentrate now on the macro procedure; provided earlier by Bertie.

Thanks again for your time and tremendous help.

Regards.