Results 1 to 7 of 7
Thread: XthDay of Month (Excel any)

20011224, 15:15 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
XthDay of Month (Excel any)
Hi All,
At Rory's requet, I am posting a workbook that calculates the Xthday 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 dayofthemonth. 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

20011224, 18:11 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XthDay 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>
Legare Coleman

20011225, 00:41 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: XthDay 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 UDFfunction 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 resultmonth was different than the basemonth  yes?
Thks again for the help.
Fred
[and here's the attachment with my version]

20011225, 00:46 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: XthDay 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

20011225, 03:05 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XthDay 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

20011225, 03:11 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: XthDay 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

20011225, 14:23 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: XthDay 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 dropdown boxes. So I assume when there's a dropdown for validation in Excel, it would work like a dropdown 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