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?

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?

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?

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?