Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
I'm not telling you to omit the "dddd", but to omit WEEKDAY.
TRIM removes spaces. For complete syntax check your help manu. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
"Tevuna" wrote: I'm not telling you to omit the "dddd", but to omit WEEKDAY. TRIM removes spaces. For complete syntax check your help manu. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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. "Tevuna" wrote: I'm not telling you to omit the "dddd", but to omit WEEKDAY. TRIM removes spaces. For complete syntax check your help manu. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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. "The Stumper" wrote: "Tevuna" wrote: I'm not telling you to omit the "dddd", but to omit WEEKDAY. TRIM removes spaces. For complete syntax check your help manu. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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" wrote in message ... 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. "Tevuna" wrote: I'm not telling you to omit the "dddd", but to omit WEEKDAY. TRIM removes spaces. For complete syntax check your help manu. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
date not recognized
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!? "Peo Sjoblom" wrote: 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" wrote in message ... 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. "Tevuna" wrote: I'm not telling you to omit the "dddd", but to omit WEEKDAY. TRIM removes spaces. For complete syntax check your help manu. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Value not recognized? | Excel Worksheet Functions | |||
All columns not being recognized in .csv file. | Excel Discussion (Misc queries) | |||
Command not recognized | Excel Worksheet Functions | |||
date format of "2005022011075" in Office 2000, is not recognized . | Excel Discussion (Misc queries) | |||
Data isn't being recognized. | Excel Worksheet Functions |