Can' get date (97)

[fburg]

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.

Fred

[LegareColeman]

Your problem is that the formula:

<pre>=MONTH(EDATE(DATEVALUE(B2 & C2),1))
</pre>


results in a month number or 11. You are then formatting this with the custom format of mmmm. The custom format expects the value in the cell to be a date serial number, not a month number. The value 11, interpreted as a date serial number is January 11, 1900. Thus, the format correctly displays January. Since the month function will always return a number between 1 and 12, you end up with date serial numbers between January 1, 1900 and January 12, 1900. With the format of mmmm, you will always get January.

If you change the formula in cell B3 to the one below, it should work:

<pre>=EDATE(DATEVALUE(B2 & C2),1)
</pre>

[LegareColeman]

What is CDate?

After rereading your question, I figured out your problem without having the CDate function. However, not having it was a bit confusing since a number of cells returned #Value errors.

[fburg]

Hi Legare

I don't think we're there yet.

On your first post, I assume your question was what was EDATE, not CDATE (I didn't see where I used the latter). I had meant to say in my post that EDATE was a function from the Analysis Toolpak for getting the date of the month following the given date. Sorry I forgot that.

On the main issue of eliminating MONTH, I set the top date to January 2002.
- the formula in both B3 and C3, w/o MONTH or YEAR evaluates to the same serial number. If B2/C2 is set to January 2002, then the serial number in B3 and C3 is 37288 (for Feb 2002). (A trick from Bob Umlas: highlight part of a formula on the formula bar and press F9 to see the result; press ESC to restore; make sure the highlighted part is a valid formula itself such as balanced parens). Not clear why C3 would be OK but not B3.
- even eliminating MONTH, which I had tried already, doesn't help the bottom group of dates. The month looks OK but the function (which you helped with about 2 weeks ago) returns #VALUE, altho it returns the proper value for the top group of dates. Further if you check what the value is in B14-B18 (the month for the bottom group), you see it is 37288. The function is expecting a string of the month, which the top group does OK. But it is getting a serial number for the bottom group. Formatting a serial number to just display the month part of the date doesn't help - the month part of the serial number has to be converted into a string.

And I just solved my problem!
I changed B3 to
=TEXT(EDATE(DATEVALUE(B2 & C2),1),"mmmm")
and it works! (also reformatted G14-18 to be a date instead of a number)

But I still don't understand why
=MONTH(EDATE(DATEVALUE(B2 & C2),1))
didn't work, given all I said above. The stuff inside EDATE(...) gives the correct serial number of the date I want. MONTH should just give me the month part of this. So worst, I woud expect TEXT(MONTH(EDATE(DATEVALUE... to have been the solution.

Thanks for your help. Still appreciate any insight as to why =MONTH... didn't work given that YEAR... does work.

Fred

[LegareColeman]

The CDate I was referring to is the one in your VBA macro. VBA was erroring out on that line and I though it was because there was no CDate function defined. I was missing that this was the VBA Convert to Date CDate, and there was a different error causing the problem.

The problems you are now having are because the cells B3 and C3 now contain date serial values. Even though B3 is formatted to display "February", when it is passed to the date_of_xth_day function, the underlying date serial value is converted to a string and passed as "02/01/2002". C3 was also getting converted to "02/01/2002" and that was being passed as the year. That means that the CDate routine was being passes "02/01/2002 1,02/01/2002" and was naturally returning the #Value error.

I have attached a workbook with changes made so that I think it now works.

[fburg]

Hi Legare,

As always, thanks for the help. I think we came to the same conclusion/fix (see my "aha" in my last post).

I should have known better when I saw the serial number in B3. I always stress the difference between the value of the cell and the display of the cell. Didn't pay attention to my own advice!

Fred