possible DATEVALUE bug, workaround needed

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

 

[Arvi Laanemets]

Hi


=E27-TODAY()

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

I do of course mean the julienne number for 1st Jan 2004!!

 

[Norman Harker]

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.

 

[eykanal]

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

 

[Harlan Grove]

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.

 

[Ron Rosenfeld]

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

 

[Dave Ellis]

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

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