date not recognized - TheStumpe

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

date not recognized - Tevun

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

date not recognized - TheStumpe

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?

date not recognized - Tevun

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

date not recognized - TheStumpe

"Tevuna" wrote:

date not recognized - TheStumpe

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.

date not recognized - TheStumpe

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.

date not recognized - 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