Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Xth-Day of Month (Excel any)

    Hi All,

    At Rory's requet, I am posting a workbook that calculates the Xth-day of the month - for example, the 2nd Tuesday of November 2001 or the 5th Thursday of December 2001.

    Pls note that this is work in progress. It was done for someone in my computer club who schedules our monthly workshops. They occur on a regular basis: Excel meets on the 3rd Wednesday, Word meets on the 4th Monday, etc. So one of his jobs, in publishing the schedule, is to get the date corresponding to the recurring day-of-the-month. I figured this was a somewhat trivial function that could be automated.

    The key is the formula that actually returns the desired date. I have chosen a particular method of input to designate the necessary parameters (the month, the year, the ordinal# of the weekday, and the weekday itself). There are probably a zillion other approaches to inputting this info - I've thought of a few myself such as a user form, allowing any valid date to be entered as 1 cell, etc. Depending on the input method, the formula might have to be adjusted slightly but basically would be the same approach.

    There is 1 known logical bug for this: if you ask for a weekday that does not exist within the requested month, a date in the following month is returned. For example, the formula returns January 1, 2002 for the 5th Tuesday of December 2001. So I was going to write a simple UDF to trap this and use Rory's IFERROR function.

    Feel free to improve on this and please post any ideas.

    Fred
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Xth-Day of Month (Excel any)

    See if this UDF does what you want. I am attaching your original workbook showing this function used.

    <pre>Public Function nthDayOfMonth(inth As Integer, strDay As String, strMonth As String, _
    iYear As Integer) As Variant
    Dim I As Long, iMonth As Integer, iDay As Integer
    Dim vMonths As Variant, vDays As Variant
    Dim datBase As Date, datResult As Date, iCnt As Integer
    vMonths = Array("January", "February", "March", "April", "May", "June", "July", _
    "August", "September", "October", "November", "December")
    vDays = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", _
    "Friday", "Saturday")
    For I = 0 To 11
    If vMonths(I) = strMonth Then Exit For
    Next I
    If I = 12 Then
    nthDayOfMonth = CVErr(xlValue)
    Exit Function
    End If
    iMonth = I + 1
    For I = 0 To 6
    If vDays(I) = strDay Then Exit For
    Next I
    If I = 7 Then
    nthDayOfMonth = CVErr(xlValue)
    Exit Function
    End If
    iDay = I + 1
    datBase = DateSerial(iYear, iMonth, 1)
    datResult = datBase + (7 * (inth - 1)) + (iDay - WeekDay(datBase) + 7) Mod 7
    If Month(datBase) <> Month(datResult) Then
    nthDayOfMonth = CVErr(xlValue)
    Else
    nthDayOfMonth = datResult
    End If
    End Function
    </pre>

    Attached Files Attached Files
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Xth-Day of Month (Excel any)

    Legare,

    Thanks much. Your UDF does do exactly what I want. But it also raises several questions.

    1. In my own code, I noticed that the VBA Mod operator works differently than the Excel Mod function for neg numbers. I adjusted for negative numbers also by adding 7, as you did. Excel Mod always returns a positive number, so it truly is looking at the multiple smaller than the first argument and returning the remainder. But where is this documented? I looked at VBA Mod help (Excel 97) and didn't see this. Is this a bug or a design element? If the latter, is there a benefit to the VBA approach over the Excel approach?

    2. My code was not operating as a UDF quite yet. Wasn't totally clear how to do this. I think I've seen posts on how to convert a sub into a UDF-function but can't recall (even changing "Sub" to "Function" didn't help). Can you point me to this? So I used message boxes for the output for now. I am attaching my version for your viewing pleasure. You can invoke the macro with CTRL+t.

    3. I noticed that your code has some assumptions about the validity of the inputs while it makes no assumptions about other inputs. For example, your code loops thru the input for the month to find a match for the month; same for day of week. If no match, than the input must have not been a valid month/day. Yet these items could not have been invalid because of the validation list back in Excel. (I realize you also get the value of I as a result of finding a match in the For loop, which is then used after the loop.) However, your code didn't check to see if the inth day was valid. If there was no validation in Excel, what would happen if it were 6 or 7 or 38? I guess this would give an error in your last IF test regardless of why the result-month was different than the base-month - yes?

    Thks again for the help.

    Fred

    [and here's the attachment with my version]
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Xth-Day of Month (Excel any)

    Legare,

    1 other question: how come autocomplete doesn't always complete? When I start typing the day of the week with the list box as a validation, some complete (like saturday) and some don't (like wednesday).

    fred

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Xth-Day of Month (Excel any)

    1- It is unfortunate, but Excel does not use the same code to implement worksheet functions and VBA functions. Therefore, you do find differences like you noted in your question. In this case I don't remember from my (long ago) math classes what the definition of MOD is for negative numbers. Therefore, I don't know which of the two are correct, or even if either of them is correct. I am inclined to think that the VBA routine is correct though.

    2- I don't know of anyplace where conveting a VBA Sub procedure into a UDF is discussed. The obvious is that the Sub procedure statement has to be changed into a Function statement (and the End Sub to an End Function). You will also most likely want to add parameters to the function to pass it its values and you should define the return type of the function. Then in the function, you must set the function name equal to its return value. You must also follow the rules for a UDF, like not trying to change anything on a worksheet except by returning a value. You should see most of what needs to be done by studying the UDF I sent to you.

    3- I try to never make assumptions about a value passed to a UDF that I write being correct. You never know when a user is going to use the function and pass a value that was not selected from the list of valid values. In this case, I think that an incorrect inth value will be caught by the check to see if the result is in the same month. If 6, 7, or 38 is passed, the result will be in a different month and that will result in a #Value error.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Xth-Day of Month (Excel any)

    Autocomplete works based on what is in other cells in the same column, not on what is in the list (it would work on what is in the list if the list box were on a user form). Therefore, Since you have Saturday, Sunday, Monday, and Tuesday in the column, those should autocomplete, but none of the other days.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Xth-Day of Month (Excel any)

    Thanks Legare.

    1. From what I remember of my math and programming classes, I think the Excel Mod is correct. The result of the division is supposed to result in a number smaller than the original number. Hence the remainder is always positive. For positive numbers this is the same for VBA and Excel. For negative numbers, VBA returns the number whose absolute value is smaller. In this sense, VBA works the same for both positive and negative numbers. For example, in VBA
    -11 Mod 7
    gives -4, obtained by just doing the division of -11 by 7, getting -1, multiplying -1x7=-7 and doing the subtraction -11 - (-7).

    But what I was wondering was whether there are advantages to the VBA approach. Probably not.

    2. I just did the conversion. Works like a champ.

    On the autocomplete, I'm used to drop-down boxes. So I assume when there's a drop-down for validation in Excel, it would work like a drop-down rather than the autocomplete of Excel that you'd get even if there was no validation. Bad assumption on my part.

    Thanks for the help.

    Fred

Posting Permissions

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