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

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

"via135" wrote in message
...
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

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.
--
David Biddulph

"via135" wrote in message

...



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

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



"via135" wrote in message
...
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.
--
David Biddulph

"via135" wrote in message

...



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

On Nov 18, 1:19 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
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

"via135" wrote in message

...



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.
--
David Biddulph

"via135" wrote in message

...

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

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



"via135" wrote in message
...
On Nov 18, 1:19 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
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

"via135" wrote in message

...



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.
--
David Biddulph

"via135" wrote in message

...

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

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

Pete

On Nov 18, 9:14 am, via135 wrote:

=datevalue(a1)

and copying it thru b10

-via135