General Format to Date Format

[weinstein.greg]

I'm using data that has been exported out of a proprietary database
into excel. The dates are formatted as "General" and no matter what I
do, I cannot change them to "Date" format (or any other format for that
matter). Has anybody encountered this?

 

[Ron Rosenfeld]

I'm using data that has been exported out of a proprietary database
into excel. The dates are formatted as "General" and no matter what I
do, I cannot change them to "Date" format (or any other format for that
matter). Has anybody encountered this?

The dates are probably TEXT. (see what you get with a formula
=ISTEXT(cell_ref)

There are a number of methods to convert them, depending on how your data is
set up.

If the data is in a column, and if you select Data/Text to Columns, you may be
able to change the format appropriately at Step 3.

If that doesn't work, post more details.
--ron

 

[weinstein.greg]

ISTEXT Formula returns a TRUE value
But the Data/Text to Columns function was not successful in changing
the format

 

[weinstein.greg]

ISTEXT Formula returns a TRUE value
But the Data/Text to Columns function was not successful in changing
the format

 

[JMay]

With your current date (as text) in A1 enter in B1:
=DATEVALUE(A1) << will return a number where Istext(B1) = FALSE
Format B1 as Date;
HTH

 

[weinstein.greg]

=Datevalue(A1) returns a #VALUE! error

With your current date (as text) in A1 enter in B1:
=DATEVALUE(A1) << will return a number where Istext(B1) = FALSE
Format B1 as Date;
HTH

 

[David Biddulph]

At the step 3 of 3 in the Data: Text to Columns wixard, did you select
"Date" in the dialogue for "Column data format", and then choose the
appropriate format "DMY" or whatever to match your input data?

 

[weinstein.greg]

Matching the format to my input data did the trick.
Thanks so much!

 

[JMay]

Your a1 cell must contain something other than (example) 9/25/06 or
'9/25/06 -- If "9/25/06" remove the " "'s using
Replace What "
With (leave Blank)
Replace all
HTH

 

[Ron Rosenfeld]

ISTEXT Formula returns a TRUE value
But the Data/Text to Columns function was not successful in changing
the format

Did you match the format at step 3 to the format of your input?
--ron