Results 1 to 15 of 17
Thread: Second Monday e.g.

072610, 10:07 #1Registered User
 Join Date
 Aug 2006
 Posts
 132
Provided Answers: 1Unanswered: Second Monday e.g.
I just did a search of dbforums and a general google search and can't find an Excel formula that will evaluate to the day number when given the Year, Weeknumber, DayName, MonthNumber.
For example; Give me the daynumber for the Second Monday of April of 2010. The answer should be 12. (We will assume that the week is SuntoSat, but that could be another variable once the basic forumula is defined).
PetePGT

072610, 10:55 #2SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
download the free online sample chapter (PDF) of the fabulous O'Reilly book SQL Hacks
scroll down to hack #23, Second Tuesday of the Month
this is one of the 6½ hacks which I contributed to the book

072610, 12:23 #3Registered User
 Join Date
 Aug 2006
 Posts
 132
Provided Answers: 1Rudy,
I just looked at your hack #23, and it looks like something that was quoted in a solution I found for use in Access. I wasn't aware that you could use a SQL statement in Excel. If you can, then I need to research how to use SQL as a cell formula.
PetePGT

072610, 12:35 #4SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
sql in a cell formula? ewww

072610, 13:51 #5Registered User
 Join Date
 Sep 2008
 Location
 London, UK
 Posts
 511
Put any valid date in the cell A1.
This formula will then tell you the date of the 2nd Monday of that month:
Code:=A1DAY(A1)+15WEEKDAY(A1DAY(A1)+6)
(Credit to "Barry Houdini" for this formula)Last edited by Colin Legg; 072610 at 13:58.

072610, 14:15 #6SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
colin, that's awesome
props to mister houdini

072610, 14:20 #7Registered User
 Join Date
 Sep 2008
 Location
 London, UK
 Posts
 511
Yeah, I never could have come up with something like that myself  my best effort would have been at least 200 characters long!
I'll let Mr Houdini know next time I see him in the pub.

072610, 22:18 #8Registered User
 Join Date
 Aug 2006
 Posts
 132
Provided Answers: 1I also was already up to too many characters trying to do a brute force logical approach.
This looks awesome. In order to figure out how to throw in some variables to get dates of other 1sts, 2nds, 3rds, etc. I parsed the formula :
A1 = serialNr of the date,
Day(A1) = gives the serialNr last day of the previous month
+ 15 = gives the serialNr of the 15th day of the current month.
WEEKDAY(A1 Day(A1) + 6) = Day of the week the 6th of the month falls on.
Encapsulating the entire formula in DAY( .), gives the Day portion of the date for the second Monday. It works ! Beautiful, but hard to decipher.
The 15 is +1 of the latest date (14) that could be a 2nd Monday (or any 2nd whatever) (e.g. 1st is on a Tues).
So assuming that you want the First of any Day you would use 8. For the 3rd of any Day you would use 22, 4th of any Day you would use 29.
I dont yet understand the logic of the 6 (even tho it obviously works). So for Sundays you would use 7, Mondays 6, Tuesdays 5, Wed 4, Thur 3, Fri 2, Sat 1.
So to Generalize the formula, if A1 = Year, B1 = Month, C1 = WeekNr, D1 = DayNr.
B3 =DATE(A1, B1, 1) {Might as well stick to 1st of month to reduce size of formula}
C3 = (C1 * 7) + 1
D3 = (8 D1)
A5 = DAY( B3 1 + C3 WEEKDAY( B3 1 + D3) )
Of course it can all be combined in one formula and further simplified.
Thanks to Colin (and of course Barry).
PetePGT

072710, 14:48 #9Registered User
 Join Date
 Jul 2010
 Posts
 2
Hello Pete,
I think you virtually deciphered the formula anyway but just in case.....
Of course once you have the 15th of the month subtracting a number between 1 and 7 will obviously give you a date in the right range.....you just need the right one.
If the 15th is a Tuesday you want to deduct 1, if it's a Wed you need to deduct 2 etc. Using WEEKDAY(15th2) will give you that.....and it turns out that 1 will work to find the 2nd Sunday, 3 for 2nd Tuesday etc......and of course WEEKDAY(15th2)=WEEKDAY(1st2) so generic formula for nth xday of the month, given 1st of the month in A1 is
=A1+n*7WEEKDAY(A1x)
where x is a number 1 to 7 representing the weekday, 1=Sun through to 7 = Sat.
I see some people use MOD in these type of formulas, you can do that also like this
=A1+7*nMOD(A1x1,7)1
but that fails if you have 1904 date system set......
Of course if you only want a number representing the day of the month you can use
=1+n*7WEEKDAY(A1x)
Colin, thanks, I appreciate the acknowledgement
regards, barry

072710, 16:30 #10Registered User
 Join Date
 Aug 2006
 Posts
 132
Provided Answers: 1My reduction of the formula came up with
=A1+(7*n)WEEKDAY(A1+7x). But then I realized the WEEKDAY number of any day 7 days away is always the same so the 7 is unnecessary. Its a good thing Colin started out with your original formula or I'd never have been able to understand it.
I ran some tests, and encapsulating your formula with DAY(.....) gives the day number of the month. However, your final formula is wrong, since it gives you the day number for the year 1900. Testing your first formula for May 2010, 2nd Tuesday, gives 11 which is correct. Testing your last formula for May 2010 gives 8 which is for 1/8/1900.
Did I make a mistake somewhere?
Pete T.PGT

072710, 18:24 #11Registered User
 Join Date
 Sep 2008
 Location
 London, UK
 Posts
 511
Hi Pete,
You didn't make a mistake. The final formula is correct because of the preceding statement it was qualified by:
Originally Posted by Barry HoudiniLast edited by Colin Legg; 072710 at 19:05.

072710, 22:50 #12Registered User
 Join Date
 Aug 2006
 Posts
 132
Provided Answers: 1Thanks to Barry and Colin for the discussion. I've learned a lot. And what's more important, I got the solution to my question.
But now its time for other things ;)
PetePGT

080310, 21:00 #13Registered User
 Join Date
 Aug 2010
 Posts
 1
I tried with your formulae but it gives me the year 1900.. please guide exact solution...

080310, 22:15 #14SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002

081510, 07:19 #15Registered User
 Join Date
 Aug 2010
 Posts
 1
On the subject of weekday etc. I need to use weeknum in a calc, but can't find my Excel CD so I can addin the Analysis Toolpack. Anyone know if there's any other way I can obtain it?
Thanks