date not recognized

[The Stumper]

I copy a word form (cntrl A then cntrl C), which contains a date in the
document, and paste (CNTL V) into an excel worksheet. In an another
worksheet within that same file I have set a formula which will tell me what
day of the week that date is. But it comes back with a "#value" error. I
can fix it by manually re-typing the exact information into the box and then
it recognizes it and returns the desired day. The formula is
"=TEXT(WEEKDAY(K32), "dddd")" where K32 equals the cell in the other
worksheet, within the file. Sounds confusing when I try to type out the
explanation. But I think I've reproted accurately. Hope someone can help.

 

[Tevuna]

I can't understand why you would need both, TEXT("dddd") and WEEKDAY() when
one does very well.
Perhaps there are issues with extra characters. Try the TRIM() function, and
format numbers as general to see if it yields a value somewhere around 39,000

 

[The Stumper]

If I leave out the "dddd" the result is a number- Looks like the number 1 for
Sunday, 2 for Monday and so on... I don't know what the TRIM function is?

 

[Tevuna]

I'm not telling you to omit the "dddd", but to omit WEEKDAY.
TRIM removes spaces. For complete syntax check your help manu.

 

[The Stumper]

I'm not telling you to omit the "dddd", but to omit WEEKDAY.
TRIM removes spaces. For complete syntax check your help manu.

 

[The Stumper]

I think it more to do with the date format of the cell it is translating. If
the copy info reads 05-21-07 it apparently doesn't see it a date because of
the "0" in 05, but when I type it in the 0 is automatically dropped. Seems
like a glitch in the program, becasue the formula otherwise works fine.

 

[The Stumper]

I think it has more to do with the way Excel integrates the copied info. I
the copied Word format the date reads 05-21-07, and I think it chokes on the
0 in 05. When I type it manually the preceeding 0 is dropped. The error
says- something in the formula is of the wrong date type. I think it is a
program error. Your help is appreciated although I may not be taking well.

 

[Peo Sjoblom]

It's because the value in K32 is text and WEEKDAY needs a number,
it's from the WORD form that you get it as text, if you type in 05-21-07
(regardless of a zero) in K32 formatted as date you will get a value
However the WEEKDAY is not necessary, you should use

=TEXT(K32,"dddd")


or just

=K32

and format the cell as dddd

WEEKDAY works by pure coincidence in this case, it's because 01-01-1900 was
a SUNDAY and WEEKDAY returns 1 for Sunday. If it had
been on a Monday you would have gotten the wrong weekday.

So what happens WEEKDAY converts the date in K32 to

01/01/1900
01/02/1900
01/03/1900
and so on until
01/07/1900

It's wrong thinking but it works by a coincidence

Nevertheless you get an error because the date is seen as text
and that is not the fault of the zero, it's WORD



--
Regards,

Peo Sjoblom

 

[The Stumper]

Thank you Peo, in the cell I want to express the day (Monday) I have entered
=TEXT(K32,"dddd") in the cell K32 I have entered ='21'!A$10 which refers to
another worksheet in which I copy info from a Word doc and A10 equals 5/18/07
and the result in the =TEXT(K32,"dddd") comes out "5/18/07". The unusaul
thing is if I go to the A10 cell and retype the contents manually (not paste
them) the formulas all work!?