| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DATEVALUE gives #VALUE! error
I have in cell A25 the text "- 6/19/2007 6:22:12 PM". The formula
=DATEVALUE((TRIM(MID(A25,FIND("-",A25)+3,11)))) is giving me a #VALUE! error. How can I get it to return the correct date? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEVALUE gives #VALUE! error
Was the text imported into Excel?
Copying your data, I got the same error as the text string for DATEVALUE had an extra character [non-viewable] on the end. using this formula, I got the date returned: =DATEVALUE((TRIM(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND("-",A1)+3,11)))) This suggests are are "hidden" ("char(160)") characters in the string which the formula substitutes with blank and hence TRIM works. HTH "hmm" wrote: I have in cell A25 the text "- 6/19/2007 6:22:12 PM". The formula =DATEVALUE((TRIM(MID(A25,FIND("-",A25)+3,11)))) is giving me a #VALUE! error. How can I get it to return the correct date? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEVALUE gives #VALUE! error
Thanks Toppers. However, I still get the error.
Any other ideas? "Toppers" wrote: Was the text imported into Excel? Copying your data, I got the same error as the text string for DATEVALUE had an extra character [non-viewable] on the end. using this formula, I got the date returned: =DATEVALUE((TRIM(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND("-",A1)+3,11)))) This suggests are are "hidden" ("char(160)") characters in the string which the formula substitutes with blank and hence TRIM works. HTH "hmm" wrote: I have in cell A25 the text "- 6/19/2007 6:22:12 PM". The formula =DATEVALUE((TRIM(MID(A25,FIND("-",A25)+3,11)))) is giving me a #VALUE! error. How can I get it to return the correct date? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEVALUE gives #VALUE! error
Can you send me sample?
toppers <at johntopley.fsnet.co.uk "hmm" wrote: Thanks Toppers. However, I still get the error. Any other ideas? "Toppers" wrote: Was the text imported into Excel? Copying your data, I got the same error as the text string for DATEVALUE had an extra character [non-viewable] on the end. using this formula, I got the date returned: =DATEVALUE((TRIM(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND("-",A1)+3,11)))) This suggests are are "hidden" ("char(160)") characters in the string which the formula substitutes with blank and hence TRIM works. HTH "hmm" wrote: I have in cell A25 the text "- 6/19/2007 6:22:12 PM". The formula =DATEVALUE((TRIM(MID(A25,FIND("-",A25)+3,11)))) is giving me a #VALUE! error. How can I get it to return the correct date? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| datevalue() | Excel Worksheet Functions | |||
| =DATEVALUE | Excel Worksheet Functions | |||
| =DATEVALUE("7/1/20"&MID(C3,13,2)) creates error | Excel Worksheet Functions | |||
| datevalue | Excel Worksheet Functions | |||
| datevalue | Excel Worksheet Functions | |||
Linear Mode
