April 1
 

Why does Friday, April 1, 2005 in a date formated cell generate a wrong data
type error when using the WEEKDAY formula?

Is it entered as a date, or as text?


"alaskanrogue (Marc George)" wrote:

Why does Friday, April 1, 2005 in a date formated cell generate a wrong data
type error when using the WEEKDAY formula?

Probably because XL doesn't recognize it as a date. It
just interprets it as a text string. If you just enter:

April 1, 2005

then XL will recognize it as a date.

To convert your current text string to a legitimate date,
use:

=--MID(G1,FIND(" ",G1)+1,1024)

and then format the date however you want.

HTH
Jason
Atlanta, GA

-----Original Message-----
Why does Friday, April 1, 2005 in a date formated cell
generate a wrong data
type error when using the WEEKDAY formula?
.

Jason Morin wrote...
Probably because XL doesn't recognize it as a date. . . .
....

Testing is good.

=WEEKDAY("April 1, 2005")

returns 6 on my system. What does it return on yours?

However,

=WEEKDAY("April 1, 2005"&CHAR(160))

returns #VALUE! even though the argument would *APPEAR* the same as the
previous argument. So I think the safer bet is that the OP has stray
nonbreaking spaces in the date string, in which case

=WEEKDAY(SUBSTITUTE("April 1, 2005"&CHAR(160),CHAR(160)," "))

returns 6.

He said "Friday, April 1, 2005". I don't know whether that means the cell
displays 'Friday' or not. If it does, then DateValue("Friday, April 1, 2005")
gives an error.




On 7 Apr 2005 12:32:42 -0700, "Harlan Grove" wrote:

Jason Morin wrote...
Probably because XL doesn't recognize it as a date. . . .
...

Testing is good.

=WEEKDAY("April 1, 2005")

returns 6 on my system. What does it return on yours?

However,

=WEEKDAY("April 1, 2005"&CHAR(160))

returns #VALUE! even though the argument would *APPEAR* the same as the
previous argument. So I think the safer bet is that the OP has stray
nonbreaking spaces in the date string, in which case

=WEEKDAY(SUBSTITUTE("April 1, 2005"&CHAR(160),CHAR(160)," "))

returns 6.

Myrna Larson wrote...
He said "Friday, April 1, 2005". I don't know whether that means the
cell
displays 'Friday' or not. If it does, then DateValue("Friday, April 1,
2005")
gives an error.
....

I overreacted. Sorry, Jason.