Convert text "date" (DDMMMYY) to datetime value of YYYY-MM-DD - ho

[j.a. harriman]

Hi,
I have a column "A" of "dates" in text of DDMMMYY format, such as "30Mar98".

I either need to convert all the values in the existing column to datetime
values (YYYY-MM-DD) or create a new column "B" that contains the converteed
values.

How is this done?

Thanks.

 

[rook]

Format-cells-custom-type:yyyy-mm-dd - ok

 

[j.a. harriman]

I right-clicked on the cell and choose the "custom" type that you mentioned
and it did nothing. I have also tried a number of the "Date" formats (by
right-clicking on the cell I want to change) - nothing.

Jeff

 

[Bob Phillips]

=DATEVALUE(A1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[rook]

When you get the custom dialog box where it says in the text field under the
place where it says type: you put in the type you want i.e. "yyyy-mm-dd"

then click OK

 

[j.a. harriman]

Bob,

I was able to get it to work, but don't know exactly how.

When I first did it, it literally had "=DATEVALUE(A1)" in the cell after I
clicked off it. A formula pop-up box appeared when I hovered over the cell
and I was able to click on the "string_text" link for the formula and must
have clicked on the actual string value and that's when it worked.

The dates are now formatted in the new column with YYYY-MM-DD, when I click
on the cell and look at the "contents" window, it indicates the "real" value
is M/DD/YYYY or MM/DD/YYYY.

Is there any way to get the "real" date in the YYYY-MM-DD format, so that
when I save this off as a TXT file or CSV that the value is YYYY-MM-DD in the
flat file?

Thanks. Jeff

 

[Bob Phillips]

You can select the column, and goto Data>Text to Columns, and click the Next
button on the first two screens of the dialog, and click the data button on
the Column data format and select a date style.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Biddulph]

=TEXT(A1,"YYYY-MM-DD")
--
David Biddulph

Bob,

I was able to get it to work, but don't know exactly how.

When I first did it, it literally had "=DATEVALUE(A1)" in the cell after I
clicked off it. A formula pop-up box appeared when I hovered over the
cell
and I was able to click on the "string_text" link for the formula and
must
have clicked on the actual string value and that's when it worked.

The dates are now formatted in the new column with YYYY-MM-DD, when I
click
on the cell and look at the "contents" window, it indicates the "real"
value
is M/DD/YYYY or MM/DD/YYYY.

Is there any way to get the "real" date in the YYYY-MM-DD format, so that
when I save this off as a TXT file or CSV that the value is YYYY-MM-DD in
the
flat file?

Thanks. Jeff