possible DATEVALUE bug, workaround needed

  • Thread starter Thread starter Eliezer
  • Start date Start date
E

Eliezer

I have an excel sheet with a bunch of dates in the future,
and I need it to calculate how far away those dates are. I
found a way which works (mostly), but the other one I
tried seems to have unearthed a bug.

The way I was trying to do it was to have access convert
the future date and today's date to their DATEVALUE values
and subtract the two:

=DATEVALUE(E27)-DATEVALUE(NOW())

where E27 would be a date located six months or whatever
in the future. However, on further examination, excel
didn't seem to be able to recognize DATEVALUE(cellnumber).
Has anyone else noticed this?
 
That's actually a lot easier than what I did (=DATEDIF(NOW
(),E27,"D"), but it doesn't give me the numbers. Also, why
doesn't the other way work?
 
DATEVALUE converts a text value to a number ie =DATEVALUE("01/01/2004") would return the Julienne number for 30th Jan 2004
 
Hi SheilaD!

They're not "julienne" numbers but Excel Date Serial Numbers.

For astronomers Julian day numbers are the number of days since
1-Jan-4713 BC with Day 0 starting UTC 12:00 Noon on 1-Jan-4713 and Day
0 starting 24 hours later. See:

http://www.tondering.dk/claus/cal/node3.html#SECTION003150000000000000000

Julian numbers to programmers are the day number of a particular year.

"Julienne" numbers are those stringy vegetables you get in posh
restaurants <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
OK, so all that is well and good, but if E27 is "1/1/2003" and I writ
=DATEVALUE(E27), it should give me the same thing as if I writ
=DATEVALUE("1/1/2003"). It doesn't, though... the second one gives m
the datevalue (37622, in case you care), but the first one gives me
#VALUE! message. Anyone know why
 
OK, so all that is well and good, but if E27 is "1/1/2003" and I write
=DATEVALUE(E27), it should give me the same thing as if I write
=DATEVALUE("1/1/2003"). It doesn't, though... the second one gives me
the datevalue (37622, in case you care), but the first one gives me a
#VALUE! message. Anyone know why?
Click to expand...

First, original formula was

=DATEVALUE(E27)-DATEVALUE(NOW())

DATEVALUE(NOW()) should return #VALUE!, which should fubar your formula. Thi is
so because DATEVALUE only accepts text strings as its argument, but NOW()
returns a date value, a number rather than a text string.

Next, you may see something in E27 that appears to be the same as "1/1/2003",
but if it's actually a date value in "m/d/yyyy" or "d/m/yyyy" format (the former
appears to be the case), then DATEVALUE(E27) will return #VALUE! for exactly the
same reason it did when passed NOW() - it just can't handle numbers, even date
numbers. Another possibility is that you have trailing nonbreaking spaces in E27
following the date string. You can check this with the formula =LEN(E27). If it
returns > 8, you have garbage space characters in E27 which you need to remove.
If it returns < 8, E27 actually contains a date number, so DATEVALUE would be
superfluous even if it did work. If it returns 8 exactly and DATEVALUE(E27)
returns #VALUE!, you may have found a bug, but since Excel doesn't do that on my
machine, it's possible Excel is just corrupted on your machine, so a reinstall
would be in order.
 
OK, so all that is well and good, but if E27 is "1/1/2003" and I write
=DATEVALUE(E27), it should give me the same thing as if I write
=DATEVALUE("1/1/2003"). It doesn't, though... the second one gives me
the datevalue (37622, in case you care), but the first one gives me a
#VALUE! message. Anyone know why?
Click to expand...

Why? It's because the contents of E27 is *NOT* the text string "1/1/2003".
The contents of E27 is probably 37622 and E27's format displays it to look like
1/1/2003.

The formula =ISTEXT(E27) probably gives a result of FALSE.


--ron
 
-----Original Message-----


Why? It's because the contents of E27 is *NOT* the text string "1/1/2003".
The contents of E27 is probably 37622 and E27's format displays it to look like
1/1/2003.

The formula =ISTEXT(E27) probably gives a result of FALSE.


--ron
.
Click to expand...


Thanks to all on this thread, particularly Ron and harlan.
I was having exactly the same misunderstanding with Excel.
This may not facilitate what I'm trying to do, but it
certainly explainde the results, or lack thereof, that I
was achieving.

Changing the cell formatting from Date to Number displays
the datenumber, but failed to ring any bells. What Harlan
said opened my eyes and Ron's input about =istext confirmed it!

Again, Thanks!

Dave
 
Back
Top