#VALUE error working with dates.

  • Thread starter Thread starter Ron Taylor
  • Start date Start date
R

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
 
Hi
have you checked that your cells contain date values and not text. Try
the following
=ISNUMBER(A2)
this should return TRUE
 
You may have some unseen garbage spaces in there. Use TRIM
to delete them:

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

HTH
Jason
Atlanta, GA
 
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
 
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
 
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'
 
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
 
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?
Click to expand...

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.
 
Back
Top