Date formula

[Guest]

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! are: 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

 

[Ron Rosenfeld]

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! are: 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 are:
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

 

[Guest]

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

 

[Peo Sjoblom]

Tools>options>transition and uncheck transition formula evaluation

--
Regards,

Peo Sjoblom

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

 

[Guest]

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!!!

 

[Ron Rosenfeld]

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!!!

You need to post more detail to describe exactly what you are doing, what
formulas and data you are using, and what your actual and expected output is.
--ron

 

[Guest]

Thank you Ron for looking back at this post
What I have is an employee timesheet. This particular one runs the
accumulated time worked for one employee an entire month. The only thing
left on the project that I am having trouble with is this date thing. Cell
C6 (Formatted ddd mmmm dd yyyy) is the input cell for the date, cell H6
(Formatted mmmm dd yy) is the expected date for the end of the month. This
cell contains your formula. Thank you for the formula!!! I placed an object,
calendar control 9.0 on the sheet and used Ron de Bruins code to reference
the active cell (C6) and place the “selected†date into that cell. After
“selecting†the appropriate date and tabbing out of the cell the calendar is
hidden. After unchecking the transition formula evaluation option, thank you
Peo, almost everything works. Because I have the object (calendar) it is
easy to “click†through the dates to see if all is working correctly. The
days in Aug including the 11th through to and including the 1st of Sept. are
the only ones that do not work properly, 22 days!! With those dates I get
##################. I get the same results without the object by inputting
the dates by hand. I get the same thing with the macros disabled. %#@#@%%&
I found the problem, and am a little shamed by it. The column was to narrow
for my formatting!!! Didn’t need to post this but thought there was a lesson
in it. Because I went through the problem as you guys requested information
I was able to find the solution. As dumb as it was I should have looked
there first. Thanks for the help guys!!!!

 

[ron]

Thank you Ron for looking back at this post
What I have is an employee timesheet. This particular one runs the
accumulated time worked for one employee an entire month. The only thing
left on the project that I am having trouble with is this date thing. Cell
C6 (Formatted ddd mmmm dd yyyy) is the input cell for the date, cell H6
(Formatted mmmm dd yy) is the expected date for the end of the month. This
cell contains your formula. Thank you for the formula!!! I placed an object,
calendar control 9.0 on the sheet and used Ron de Bruins code to reference
the active cell (C6) and place the “selected†date into that cell. After
“selecting†the appropriate date and tabbing out of the cell the calendar is
hidden. After unchecking the transition formula evaluation option, thank you
Peo, almost everything works. Because I have the object (calendar) it is
easy to “click†through the dates to see if all is working correctly. The
days in Aug including the 11th through to and including the 1st of Sept. are
the only ones that do not work properly, 22 days!! With those dates I get
##################. I get the same results without the object by inputting
the dates by hand. I get the same thing with the macros disabled. %#@#@%%&
I found the problem, and am a little shamed by it. The column was to narrow
for my formatting!!! Didn’t need to post this but thought there was a lesson
in it. Because I went through the problem as you guys requested information
I was able to find the solution. As dumb as it was I should have looked
there first. Thanks for the help guys!!!!


:

I'm glad you got things working to your satisfaction

ron