1. Registered User
Join Date
Aug 2006
Posts
132

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 Sun-to-Sat, but that could be another variable once the basic forumula is defined).
Pete

2. SQL Consultant
Join Date
Apr 2002
Location
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

3. Registered User
Join Date
Aug 2006
Posts
132
Rudy,
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.
Pete

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
sql in a cell formula? ewww

5. Registered 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:
`=A1-DAY(A1)+15-WEEKDAY(A1-DAY(A1)+6)`

(Credit to "Barry Houdini" for this formula)
Last edited by Colin Legg; 07-26-10 at 14:58.

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
colin, that's awesome

props to mister houdini

7. Registered 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.

8. Registered User
Join Date
Aug 2006
Posts
132
I 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 dont 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).

Pete

9. Registered 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(15th-2) 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(15th-2)=WEEKDAY(1st-2) so generic formula for nth xday of the month, given 1st of the month in A1 is

=A1+n*7-WEEKDAY(A1-x)

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*n-MOD(A1-x-1,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*7-WEEKDAY(A1-x)

Colin, thanks, I appreciate the acknowledgement

regards, barry

10. Registered User
Join Date
Aug 2006
Posts
132
My reduction of the formula came up with
=A1+(7*n)-WEEKDAY(A1+7-x). 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.

11. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi Pete,
Originally Posted by Pete Townsend

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.
You didn't make a mistake. The final formula is correct because of the preceding statement it was qualified by:
Originally Posted by Barry Houdini
if you only want a number representing the day of the month
As you said, Pete, if you are using the Excel 1900 date system then 8th January 1900 is numerically represented by 8. The formula was built to retrieve a number representing the day of the month - not a date - so it's just a question of formatting the cell correctly.
Last edited by Colin Legg; 07-27-10 at 20:05.

12. Registered User
Join Date
Aug 2006
Posts
132
Thanks 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 ;-)
Pete

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

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by shobazee
I tried with your formulae but it gives me the year 1900.. please guide exact solution...
please show the exact formula that you tried

15. Registered 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 add-in the Analysis Toolpack. Anyone know if there's any other way I can obtain it?
Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•