Date Formulae - Working Days and Specific Day Of Month

[Richard Schollar]

Hi All

1. What Formula could I use to identify the date of a specific day in a month - in this case the 3rd Wednesday of a month?

2. Also, what formula could I use to identify the date of say the 9th Working Day (ie non-Saturday/Sunday) of a given month? I want this to be generic so that 9th could be replaced eg with 15th, 20th etc.

I would appreciate solutions both with AND without the Analysis Toolpak Add-In functions.

Many thanks for your time.

Richard

 

[hatman]

Part 1:

Exceprt from my Departmental Vacation Tracker:

2006a vacation.xls
	A	B	C	D	E	F	G	H	I
1	2006	EnterYearHere
2
3	Holidays
4		calculateddate	datebreakdown			TextDate	inputtocalculate
5			Month	Day	Year		week#	day	Month
6
7	MartinLutherKing	1/16/2006	1	16	2006	1/16	3	mon	1
8	Washington'sB-Day	2/20/2006	2	20	2006	2/20	3	mon	2
9	Easter	4/16/2006	4	16	2006	4/16	special
10	MemorialDay	5/29/2006	5	29	2006	5/29	last	mon	5
Holidays

Simply enter the Week Number, Day of Wek, and Month number, and it does the rest... also sensitive to the term "last"...

EDIT: I always forget the Amp problem: here is the formula in B7...

=IF(G7="last",IF(I7+1=13,"1/1/"&E7+1,I7+1&"/1/"&E7)-1-(WEEKDAY(IF(I7+1=13,"1/1/"&E7+1,I7+1&"/1/"&E7)-1)-VLOOKUP(H7,BTC!$E$5:$G$11,3,FALSE)+IF(WEEKDAY(IF(I7+1=13,"1/1/"&E7+1,I7+1&"/1/"&E7)-1)-VLOOKUP(H7,BTC!$E$5:$G$11,3,FALSE)<0,7,0)),DATE(E7,I7,1+((G7-(VLOOKUP(H7,BTC!$E$5:$G$11,3,FALSE)>=WEEKDAY(DATE(E7,I7,1))))*7)
+(VLOOKUP(H7,BTC!$E$5:$G$11,3,FALSE)-WEEKDAY(DATE(E7,I7,1)))))

Not pretty, but it works well, and has been in use for a few years now...

 

[hatman]

And lastly, as I review this formula, I realize I'm referencing a table on a another sheet... here is THAT structure:

2006a vacation.xls
	E	F	G	H	I
1	Lookup#DaysinFirstorLastWeek
2	day	totaldaysinweek		sat/sun
3		first	last	first	last
4			0		0
5	Sun	7	1	2	1
6	Mon	6	2	1	1
7	Tue	5	3	1	1
8	Wed	4	4	1	1
9	Thu	3	5	1	1
10	Fri	2	6	1	1
11	Sat	1	7	1	2
BTC

(Hmm, guess this method is a little less compact than I remembered it...)

 

[barry houdini]

Hello Richard

If you have the 1st of the month in A1 then to give the 3rd Wednesday of that month

=A1-WEEKDAY(A1-4)+3*7

where 4 indicates Wednesday and 3 the occurrence. Of course if you use 6 as the occurrence you'll get a date in the next month so you'd need some sort of IF function perhaps to avoid that.

For the 9th Workday this formula will do what you want, again with 1st of the month in A1

=SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&DATE(YEAR(A1),MONTH(A1)+1,0))),2)<6,ROW(INDIRECT(A1&":"&DATE(YEAR(A1),MONTH(A1)+1,0)))),9)

confirmed with CTRL+SHIFT+ENTER

In this case if you ask for a workday which doesn't exist, e.g. change the 9 for 24 then you'll get a #NUM! error.

There's an easier way to do this 2nd one, without an array formula, I'll get back to you if I can remember what it is

 

[Richard Schollar]

Paul - thanks very much for that. That is one mammoth formula BTW!! I looked at it when you first posted it, and thought to myself 'Hmm, wonder what the VLOOKUP does?' - I'm glad you posted the follow up!

Barry - I'm humbled, as ever, by your formula prowess. I love the '3rd Wednesday in a month' formula - I am amazed it is so simple. The nth working day of the month formula is very useful to me too - many thanks.

 

[hatman]

Barry - I'm humbled, as ever, by your formula prowess. I love the '3rd Wednesday in a month' formula - I am amazed it is so simple. The nth working day of the month formula is very useful to me too - many thanks.

Barr6y, as ever, you make it look so easy. Now I feel compelled to go back and rework my own project. Though Memorial Day still becomes a problem, as it's the LAST Monday of the month, which may be the 4th or 5th Monday, depending upon how the year fals out...

 

[barry houdini]

Though Memorial Day still becomes a problem, as it's the LAST Monday of the month, which may be the 4th or 5th Monday, depending upon how the year fals out...

....but whether its the 4th or 5th Monday of May it's always 7 days before the first Monday in June so if you have the year in A1 this gives you the date of Memorial Day for that year

=DATE(A1,6,1)-WEEKDAY(DATE(A1,6,6))

 

[hatman]

Though Memorial Day still becomes a problem, as it's the LAST Monday of the month, which may be the 4th or 5th Monday, depending upon how the year fals out...

....but whether its the 4th or 5th Monday of May it's always 7 days before the first Monday in June so if you have the year in A1 this gives you the date of Memorial Day for that year

=DATE(A1,6,1)-WEEKDAY(DATE(A1,6,6))

HA HAH!

Of course. So simple when you put it that way.