#1   Report Post  
Old April 7th 05, 04:05 PM
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  
Old April 7th 05, 04:13 PM
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  
Old April 7th 05, 05:15 PM
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  
Old April 7th 05, 08:32 PM
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  
Old April 8th 05, 02:06 AM
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  
Old April 8th 05, 05:40 PM
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 12th 05 11:11 PM
Accounting Month vs. Calendar Month JN Excel Worksheet Functions 4 January 31st 05 08:09 PM
Years when 1 April occurs on a Saturday R B Excel Worksheet Functions 5 November 6th 04 10:01 AM


All times are GMT +1. The time now is 02:12 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017