Ron
Thanks for your return. I obviously have other things going on. I tried
your formula and got the #NUM! error, went back to my old formula and it is
working now on different day than it did before, but not all!!! don't ask
me...Let me run down some of your other suggustions and I will past back..
thank again

"Ron Rosenfeld" wrote:

On Mon, 17 Jan 2005 15:51:01 -0800, Mike R
wrote:

Hello All:
I have a spreadsheet that I need to have the dates for the month. On this
application I needed the First of the month in one cell and the last day of
the month listed in another. I inserted a calendar that allows me to put the
selected date into the active cell. (Formatted ddd mmmm dd yyyy) The I use
the formula =DATE(YEAR(C6),MONTH(C6)+1,DAY(C6)-1) to enter the last day of
the month into the appropriate cell. (Formatted mmmm dd yy) The problem I am
having is that this formula works on most of the dates in a month but gives
me a #NUM! Error when it is asked to give me the last day of the month. I
other words if I select the first day of a month it gives me #NUM! If I
select the second day of the month it returns the first day of the next
month, if I select the 15th it returns the 14th of the next month. So it
seems to give me the next month less on one day as I want in most cases
except the first day of the month. The other thing that is perplexing is
that it did work at one time, so I am thinking I have done something to
change it but for the life of me I cannot figure out what. The dates that
return #NUM! a Jan 30 & 31, Feb1, March 1, April 1, May 1, June 1, July 1,
August 1, (Aug 11 thru 31 returns ##############), Sept 1, Oct 1, Nov 1, and
all of the month of Dec 2005. Is this a must be a corrupt file!!!!! I have
deleted the object and reinserted it and there was no change. Help!!!
I am using xl2000

I cannot follow what you are doing, nor can I reproduce your errors by doing
what I think you are saying you are doing.

In Excel, select usually means to move the cursor to a particular cell or
object. But that does not make sense in your context.

I have entered your formula into a cell, and entered various dates into C6, and
your formula gives me the date one month in advance of the date in C6, less one
day. Depending on the relative numbers of days in the months, this is not
necessarily the last day of the month.

If you want a formula for the last day of the month of the date that you enter
into C6, that formula would be: =DATE(YEAR(C6),MONTH(C6)+1,0)

Beyond that, I cannot follow what it is you are trying to do.

But some places to look for possible problems a
1. Are the cells formatted as text before you enter the formula?
2. Are your Regional Settings (Control Panel) the same format as the
dates you are entering?
3. Is the "seed" date entered directly or is it computed? If it is
the result of the formula, is the formula returning a string or a true Excel
date?


--ron