Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2006
    Posts
    126

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

  2. #2
    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


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    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
    PGT

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql in a cell formula? ewww

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    colin, that's awesome

    props to mister houdini
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    Join Date
    Aug 2006
    Posts
    126
    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
    PGT

  9. #9
    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. #10
    Join Date
    Aug 2006
    Posts
    126
    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.
    PGT

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Pete,
    Quote Originally Posted by Pete Townsend View Post

    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:
    Quote 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. #12
    Join Date
    Aug 2006
    Posts
    126
    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
    PGT

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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shobazee View Post
    I tried with your formulae but it gives me the year 1900.. please guide exact solution...
    please show the exact formula that you tried
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    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
  •