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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
=DATEVALUE(A1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
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
 
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)
 
=TEXT(A1,"YYYY-MM-DD")
--
David Biddulph

j.a. harriman said:
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
Click to expand...
 
Back
Top