i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike

My guess is that what you think is a date is actually text.
--
David Biddulph

"Mike" wrote in message
...
i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is
correct.
Any suggestions?

Mike

Thanks Dave but the target cell is formatted as a date.

"David Biddulph" wrote:

My guess is that what you think is a date is actually text.
--
David Biddulph

"Mike" wrote in message
...
i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is
correct.
Any suggestions?

Mike

On Sat, 11 Nov 2006 12:02:01 -0800, Mike
wrote:

i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike


The VALUE error frequently indicates a problem with your *data*.

Could it be that you have imported the date from an HTML or other web source?
If so, it is probably text with a trailing <no break space character.

Try this:

=EOMONTH(SUBSTITUTE(TRIM(A1),CHAR(160),""),1)


--ron

Ron,

The suggestion you gave me produced the same error. To be absolutely sure
there is nothing wrong with the dates I deleted Cell A1 contents, formatted
the cell as a date (again) and entered the date "1/11/06" no quotes. Using
the formula =eomonth(a1,1) still returned a #value! error instead of what I
expected 30/12/06.

"Ron Rosenfeld" wrote:

On Sat, 11 Nov 2006 12:02:01 -0800, Mike
wrote:

i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike


The VALUE error frequently indicates a problem with your *data*.

Could it be that you have imported the date from an HTML or other web source?
If so, it is probably text with a trailing <no break space character.

Try this:

=EOMONTH(SUBSTITUTE(TRIM(A1),CHAR(160),""),1)


--ron

The cell may well be formatted as a date but contain text. What do you see
if you enter, in another cell, =A1+1 ?
--
David Biddulph

"Mike" wrote in message
...
Thanks Dave but the target cell is formatted as a date.

"David Biddulph" wrote:

My guess is that what you think is a date is actually text.
--
David Biddulph

"Mike" wrote in message
...
i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month
I'm
getting a "#value!" error despite being fairly sure the formula is
correct.
Any suggestions?

Mike

On Sat, 11 Nov 2006 12:43:02 -0800, Mike
wrote:

Ron,

The suggestion you gave me produced the same error. To be absolutely sure
there is nothing wrong with the dates I deleted Cell A1 contents, formatted
the cell as a date (again) and entered the date "1/11/06" no quotes. Using
the formula =eomonth(a1,1) still returned a #value! error instead of what I
expected 30/12/06.

I cannot reproduce the problem you report on my system. But I would expect to
get 31/12/06 as a result (which I do).

What are your Language settings?
(I tried English-GB to get the DMY format)

What are the return values for these formulas:

=LEN(A1)

=ISTEXT(A1)

=EOMONTH(DATE(2006,11,1),1)

They should return

5
FALSE
31 DEC 2006 (depending on format)


--ron

Sorry Dave missed this reply. using your formula adds 1 day to the date

Mike

"David Biddulph" wrote:

The cell may well be formatted as a date but contain text. What do you see
if you enter, in another cell, =A1+1 ?
--
David Biddulph

"Mike" wrote in message
...
Thanks Dave but the target cell is formatted as a date.

"David Biddulph" wrote:

My guess is that what you think is a date is actually text.
--
David Biddulph

"Mike" wrote in message
...
i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month
I'm
getting a "#value!" error despite being fairly sure the formula is
correct.
Any suggestions?

Mike

Ron,

=len(A1) returns 5
=istext(a1) returns false

I suspect that this confirms that it is in fact a date am I correct?
Thanks for your perseverance.

Mike

"Ron Rosenfeld" wrote:

On Sat, 11 Nov 2006 12:43:02 -0800, Mike
wrote:

Ron,

The suggestion you gave me produced the same error. To be absolutely sure
there is nothing wrong with the dates I deleted Cell A1 contents, formatted
the cell as a date (again) and entered the date "1/11/06" no quotes. Using
the formula =eomonth(a1,1) still returned a #value! error instead of what I
expected 30/12/06.

I cannot reproduce the problem you report on my system. But I would expect to
get 31/12/06 as a result (which I do).

What are your Language settings?
(I tried English-GB to get the DMY format)

What are the return values for these formulas:

=LEN(A1)

=ISTEXT(A1)

=EOMONTH(DATE(2006,11,1),1)

They should return

5
FALSE
31 DEC 2006 (depending on format)


--ron

pls. chk. analysis tool pack as an addin

"Mike" wrote:

i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike

Ron, sorry should have read your post correctly. the last one
=EOMONTH(DATE(2006,11,1),1) returns a #value! error

Mike

"Ron Rosenfeld" wrote:

On Sat, 11 Nov 2006 12:43:02 -0800, Mike
wrote:

Ron,

The suggestion you gave me produced the same error. To be absolutely sure
there is nothing wrong with the dates I deleted Cell A1 contents, formatted
the cell as a date (again) and entered the date "1/11/06" no quotes. Using
the formula =eomonth(a1,1) still returned a #value! error instead of what I
expected 30/12/06.

I cannot reproduce the problem you report on my system. But I would expect to
get 31/12/06 as a result (which I do).

What are your Language settings?
(I tried English-GB to get the DMY format)

What are the return values for these formulas:

=LEN(A1)

=ISTEXT(A1)

=EOMONTH(DATE(2006,11,1),1)

They should return

5
FALSE
31 DEC 2006 (depending on format)


--ron

4pinoy,

There is now a very embarrassed silence from here. add-in pack checked and
the function now works as expected. Just 1 point I have just checked
Microsoft help and it says I should get a ?name error if the add-in isn't
checked.

Thanks everone for their help.

Mike

"4pinoy" wrote:

pls. chk. analysis tool pack as an addin

"Mike" wrote:

i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike

Do you have your own UDF called EOMONTH?
What happens if you press F5 and fill in EOMONTH ?
What happens if you use the Insert Function Wizard *in an empty cell* to insert EoMonth?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Mike" wrote in message ...
| Ron,
|
| =len(A1) returns 5
| =istext(a1) returns false
|
| I suspect that this confirms that it is in fact a date am I correct?
| Thanks for your perseverance.
|
| Mike
|
| "Ron Rosenfeld" wrote:
|
| On Sat, 11 Nov 2006 12:43:02 -0800, Mike
| wrote:
|
| Ron,
|
| The suggestion you gave me produced the same error. To be absolutely sure
| there is nothing wrong with the dates I deleted Cell A1 contents, formatted
| the cell as a date (again) and entered the date "1/11/06" no quotes. Using
| the formula =eomonth(a1,1) still returned a #value! error instead of what I
| expected 30/12/06.
|
| I cannot reproduce the problem you report on my system. But I would expect to
| get 31/12/06 as a result (which I do).
|
| What are your Language settings?
| (I tried English-GB to get the DMY format)
|
| What are the return values for these formulas:
|
| =LEN(A1)
|
| =ISTEXT(A1)
|
| =EOMONTH(DATE(2006,11,1),1)
|
| They should return
|
| 5
| FALSE
| 31 DEC 2006 (depending on format)
|
|
| --ron
|

it just happen to have the same problem before....

"Mike" wrote:

4pinoy,

There is now a very embarrassed silence from here. add-in pack checked and
the function now works as expected. Just 1 point I have just checked
Microsoft help and it says I should get a ?name error if the add-in isn't
checked.

Thanks everone for their help.

Mike

"4pinoy" wrote:

pls. chk. analysis tool pack as an addin

"Mike" wrote:

i have dates in a column and in the next column im using the formula
=eomonth(a1,1) and instead of getting the last day of the next month I'm
getting a "#value!" error despite being fairly sure the formula is correct.
Any suggestions?

Mike

On Sat, 11 Nov 2006 13:12:01 -0800, Mike
wrote:

Ron, sorry should have read your post correctly. the last one
=EOMONTH(DATE(2006,11,1),1) returns a #value! error

Mike


That being the case, it is likely that you are not calling the Analysis ToolPak
EOMONTH function, but perhaps some other function with the same name.

Ordinarily, if the ATP is not installed, you would get the #NAME error. But if
you had another UDF with the same name, you might be getting a VALUE error.

Check if the Analysis Tool Pak is installed.
--ron