EOMONTH() question

[Guest]

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

 

[David Biddulph]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[David Biddulph]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

Mike

 

[Guest]

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

 

[Guest]

pls. chk. analysis tool pack as an addin

 

[Guest]

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

Mike

 

[Guest]

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

 

[Niek Otten]

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


| 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 <[email protected]>
| > 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
| >

 

[Guest]

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

 

[Ron Rosenfeld]

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