possible DATEVALUE bug, workaround needed

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?

Hi


=E27-TODAY()


--
(When sending e-mail, use address )
Arvi Laanemets


"Eliezer" wrote in message
...
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?

-----Original Message-----
Hi


=E27-TODAY()


--
(When sending e-mail, use address )
Arvi Laanemets


"Eliezer" wrote in
message
...
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?


.

DATEVALUE converts a text value to a number ie =DATEVALUE("01/01/2004") would return the Julienne number for 30th Jan 2004

I do of course mean the julienne number for 1st 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/no...00000000000000

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

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 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?


---
Message posted from http://www.ExcelForum.com/

"eykanal " wrote...
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?

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.

--
To top-post is human, to bottom-post and snip is sublime.

On Fri, 30 Jan 2004 12:19:12 -0600, eykanal
wrote:

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?


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-----
On Fri, 30 Jan 2004 12:19:12 -0600, eykanal
wrote:

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?


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
.



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