A few weeks ago, I had a thread about getting the date of the xth day of a month (eg, return the date for the 4 Monday of December 2001). I posted a worksheet solution that fell a little short of a full solution (it gave January 1, 2002 as the 5th Tuesday of December 2001) and Legare finished it up with some VBA that corrected this annoyance.
Now I'm trying to put this into a spreadsheet that our computer-club meeting scheduler can use. We have groups that nominally meet on a particular day of the month. So, Excel (my favorite, of course) meets on the 3rd Wednesday and Word meets on the 4th Monday, etc. (The fact that the actual date may change is not relevant here.) He schedules things for 2 months at a time. So, for, say, January (in our newsletter and on our website) there'd be one line for each group for January 2001 and the groups would be repeated for February 2001. In February, we'd show February and March.
See the attached for a sample.
What I'd like to do is just input the first month/year of the two-month period. The nominal days (3rd Wednesday) are always the same, so these would not change. Everything else can be derived, including the 2nd of the 2 months.
But I seem to be having a problem getting the 2nd month to come out properly. If you look at the 1st sheet (SIG Schedule), you'll see the 1st month and year as specified in cells B2 and C2. The 2nd month is derived in cell B3 from the first month/year combination while the 2nd year is derived in C3 also from B2 and C2. I have tried several different approaches to getting the month in B3 to no avail.
For example, the formula currently in B3 gives January. But if you examine the serial number of the partial formula of B3, it gives 37561, same as C3 (highlight the material inside of =MONTH() or =YEAR() and press F9). Yet the entire formula returns January. I know that 37561 corresponds to 11/1/02 since I tested G8 and got 37530 for Oct 1, 2002.
If I format B3 as General or a number, I get 11.
In fact, it doesn't matter what I change B2 to, I still get January in B3. However, the year in C3 seems to work correctly.
It also doesn't matter if I change the formula in B3 to look like that in C3 - using TEXT() instead of DATEVALUE(). Same thing - January.
The second set of meetings, in rows 14-18 therefore come out wrong.
I'm going crazy here figuring out what the problem is.
TIA for any help.