#1   Report Post  
alaskanrogue (Marc George)
 
Posts: n/a
Default April 1

Why does Friday, April 1, 2005 in a date formated cell generate a wrong data
type error when using the WEEKDAY formula?
  #2   Report Post  
Duke Carey
 
Posts: n/a
Default

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?

  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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

  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #5   Report Post  
Myrna Larson
 
Posts: n/a
Default

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.




  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting formula Jack Sons Excel Discussion (Misc queries) 6 April 5th 05 09:50 AM
Brainteaser about Days Between Dates Johnny Excel Discussion (Misc queries) 5 April 4th 05 05:09 AM
How do I get networkdays for a month pbs Excel Worksheet Functions 3 February 13th 05 12:11 AM
Accounting Month vs. Calendar Month JN Excel Worksheet Functions 4 January 31st 05 09:09 PM
Years when 1 April occurs on a Saturday R B Excel Worksheet Functions 5 November 6th 04 11:01 AM


All times are GMT +1. The time now is 05:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"