DATEVALUE gives #VALUE! error

[hmm]

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?

 

[Toppers]

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]

Thanks Toppers. However, I still get the error.

Any other ideas?

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

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?

 

[Toppers]

Can you send me sample?

toppers <at> johntopley.fsnet.co.uk

Thanks Toppers. However, I still get the error.

Any other ideas?

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

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?