#VALUE error working with dates.

[Ron Taylor]

Excel 2002 (10.6501.6626) SP3
I have entered the following data:
A2 6/17/2002
A3 4/01/2002

The formula: =MONTH(A2)-MONTH(A3)
The results: #VALUE

This happens in all my date functions, DAY, MONTH, YEAR. What could cause
this?

Ron
Scanned by eTrust EZ Antivirus

 

[Frank Kabel]

Hi
have you checked that your cells contain date values and not text. Try
the following
=ISNUMBER(A2)
this should return TRUE

 

[Jason Morin]

You may have some unseen garbage spaces in there. Use TRIM
to delete them:

=MONTH(TRIM(A2))-MONTH(TRIM(A3))

HTH
Jason
Atlanta, GA

 

[Ron Taylor]

It comes FALSE. I changed the format but it still comes FALSE. This is what
I did. I have and online account and I copied part of the page and inserted
it into Excel. If I type in the date my formula works on the typed data. But
I can't seem to change the data type on the imported cells. There are just
too many to retype.
Thanks for the suggestion.


Ron
Scanned by eTrust EZ Antivirus

 

[Ron Taylor]

It comes #VALUE. I changed the format but it still comes #VALUE. This is
what I did. I have and online account and I copied part of the page and
inserted it into Excel. If I type in the date my formula works on the typed
data. But I can't seem to change the data type on the imported cells. There
are just too many to retype.
Thanks for the suggestion.
Ron
Scanned by eTrust EZ Antivirus

 

[Frank Kabel]

Hi Ron
so your dates are stored as Text. try the following:
- choose a date format for this range
- copy an empty cell with CTRL+C
- select the date range again
- goto 'Edit - Paste Special' and choose the action 'Add'

 

[Ron Taylor]

Thanks for the suggestion, I tried it and no go. But I did fix the problem.
I saved the data in a Word text file. Then I opened it in Unicode and found
the hidden characters. I deleted all these and then saved it in a different
text file. Then I imported the file as data into Excel and that did the
trick. A little around the horn but I got what I needed.
Thanks again.
Ron
Scanned by eTrust EZ Antivirus

 

[Harlan Grove]

Excel 2002 (10.6501.6626) SP3
I have entered the following data:
A2 6/17/2002
A3 4/01/2002

The formula: =MONTH(A2)-MONTH(A3)
The results: #VALUE

This happens in all my date functions, DAY, MONTH, YEAR. What could cause
this?

Having read the other responses, especially your follow-up in which you
mentioned these dates come from an online source, it seems your dates may
include stray NONBREAKING spaces (char code 160 decimal). Try

=MONTH(TRIM(SUBSTITUTE(A2,CHAR(160),"")))
-MONTH(TRIM(SUBSTITUTE(A3,CHAR(160),"")))

If that works, then best to use Edit > Replace to remove the spaces (breaking,
aka ASCII, and nonbreaking). Replace all instances of <space> (hit the spacebar
once) with nothing to remove all breaking/ASCII spaces. For nonbreaking spaces,
in the 'Find what' field hold down an [Alt] key and press in sequence 0, 1, 6, 0
on the numberic keypad (*NOT* the typewriter keys above the QWERTY row!) and
release the [Alt] key. That should enter a nonbreaking space. Replace all
instances of it with nothing.