converting text strings into dates

[via135]

i'm having a1:a10 dates. but when i check it out whether they are real
dates by using datevalue function thru b1:b10 some of them giving
#value errors. how can i convert all of them to real dates?


-via135

 

[David Biddulph]

It depends which format you have your text strings, but it may be worth
trying Data/ Text to Columns/ and at the final stage of the Wizard specify
the format of the date from which you are starting (e.g. DMY).

If you've got additional characters such as extra spaces, commas, etc., then
you may find you need to remove them before they will be accepted as dates.

 

[via135]

It depends which format you have your text strings, but it may be worth
trying Data/ Text to Columns/ and at the final stage of the Wizard specify
the format of the date from which you are starting (e.g. DMY).

If you've got additional characters such as extra spaces, commas, etc., then
you may find you need to remove them before they will be accepted as dates.
--
David Biddulph







- Show quoted text -

i've tried with TTC as well as the TRIM function but all in vain..
any other clue?

-via135

 

[Roger Govier]

Hi

Data Text to Columns normally resolves such matters.
What is the formula you are using in B1, which is telling you that they are
not true Excel dates?

 

[via135]

Hi

Data Text to Columns normally resolves such matters.
What is the formula you are using in B1, which is telling you that they are
not true Excel dates?

--
Regards
Roger Govier








- Show quoted text -

=datevalue(a1)

and copying it thru b10

-via135

 

[Roger Govier]

Hi

=datevalue(a1)

and copying it thru b10

If the value in A1 is a true Excel date, then you will get a #VALUE error.
If you want to test if it is a true date, then
=A1+0
will return the same date if true, or an error if not.

--
Regards
Roger Govier

Hi

Data Text to Columns normally resolves such matters.
What is the formula you are using in B1, which is telling you that they
are
not true Excel dates?

--
Regards
Roger Govier

On Nov 18, 12:56 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
It depends which format you have your text strings, but it may be
worth
trying Data/ Text to Columns/ and at the final stage of the Wizard
specify
the format of the date from which you are starting (e.g. DMY).

If you've got additional characters such as extra spaces, commas,
etc.,
then
you may find you need to remove them before they will be accepted as
dates.

i'm having a1:a10 dates. but when i check it out whether they are
real
dates by using datevalue function thru b1:b10 some of them giving
#value errors. how can i convert all of them to real dates?

-via135- Hide quoted text -

- Show quoted text -

i've tried with TTC as well as the TRIM function but all in vain..
any other clue?

-via135- Hide quoted text -

- Show quoted text -

=datevalue(a1)

and copying it thru b10

-via135

 

[Pete_UK]

Give some examples of the data you have in column A, so we can see the
variations in the formats used.

Pete