Peo
Thank you for the post!!! That solved 95% of the problems. Now, with Ron's
last day of the month formula everything works with the ecception of Aug 11
thru 31 and Sept 1. When I go back to my original formula all works with the
exception of the month of Sept. I am beginning to think I am supposed to
just take vacation in late summer and not try to use this worksheet!!!! any
other words of wisdom? (Beside just take the vacation, although that is an
option I would like this sheet to work). Thank you again for the help!!!

"Peo Sjoblom" wrote:

Toolsoptionstransition and uncheck transition formula evaluation

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Mike R" wrote in message
...
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