Hi,

I have a file with a date column that appears right on the screen. That is,
07-04-04, meaning, 2007 April 4th.
The thing is when I change the date format it turns to 2004-April-07!
Is there any way to change this without having to retype all the dates??

Thanks very much for any help.
Barbara

First, 07-04-04 is still pretty ambiguous. Even when you say it should be April
4, 2007, does that mean that the first 04 is the month or the last 04 is the
month.

Second, this is what I'd try.

Insert a couple of extra columns to the right of the column with the dates.
In the first helper column (column B), I'd put a formula that just echoes the
value in the original column:

Assuming the dates are in column A (A1:A###)
=A1
but give this a nice unambiguous date format--like yyyy mmmm dd.
and drag down.

You may find that some of your "dates" aren't really dates--they don't react to
the formatting.

Then I'd use this formula in the second helper column (Column C):

=text(b1,"yy-mm-dd")
and drag down.

See if those text values match how you want to interpret the dates in the
original column.

If they don't match, then you'll have to fix them.

But after they're done, you can
convert to values
select column C
edit|copy
edit|paste special|values

Then (with column C) still selected,
Data|Text To columns
fixed width (but don't have any separator lines)
and choose date (ymd) for that field
and plop it back into column C.

Then format this column in an unambiguous format and check those dates to see if
they really are what they should be. Just because they're dates, doesn't mean
they'll be the dates that you expect.

==========
ps.

If these dates are being imported from a text file, it might be easier to
reimport the data and correctly specify the mdy order of each date field.


Barbara wrote:

Hi,

I have a file with a date column that appears right on the screen. That is,
07-04-04, meaning, 2007 April 4th.
The thing is when I change the date format it turns to 2004-April-07!
Is there any way to change this without having to retype all the dates??

Thanks very much for any help.
Barbara

--

Dave Peterson

Hi Dave,
I found out from where the user got that file!!! txt! so I just imported
again to Excel giving all columns General formating and it worked just fine.

Thank you for your reply.
Barbara

"Dave Peterson" wrote:

First, 07-04-04 is still pretty ambiguous. Even when you say it should be April
4, 2007, does that mean that the first 04 is the month or the last 04 is the
month.

Second, this is what I'd try.

Insert a couple of extra columns to the right of the column with the dates.
In the first helper column (column B), I'd put a formula that just echoes the
value in the original column:

Assuming the dates are in column A (A1:A###)
=A1
but give this a nice unambiguous date format--like yyyy mmmm dd.
and drag down.

You may find that some of your "dates" aren't really dates--they don't react to
the formatting.

Then I'd use this formula in the second helper column (Column C):

=text(b1,"yy-mm-dd")
and drag down.

See if those text values match how you want to interpret the dates in the
original column.

If they don't match, then you'll have to fix them.

But after they're done, you can
convert to values
select column C
edit|copy
edit|paste special|values

Then (with column C) still selected,
Data|Text To columns
fixed width (but don't have any separator lines)
and choose date (ymd) for that field
and plop it back into column C.

Then format this column in an unambiguous format and check those dates to see if
they really are what they should be. Just because they're dates, doesn't mean
they'll be the dates that you expect.

==========
ps.

If these dates are being imported from a text file, it might be easier to
reimport the data and correctly specify the mdy order of each date field.


Barbara wrote:

Hi,

I have a file with a date column that appears right on the screen. That is,
07-04-04, meaning, 2007 April 4th.
The thing is when I change the date format it turns to 2004-April-07!
Is there any way to change this without having to retype all the dates??

Thanks very much for any help.
Barbara

--

Dave Peterson

Be careful with a General format.

If the cell looks like a date, excel will treat it like a date. You may be
better re-importing the file once more and specifying the field the way you know
it should be.

Barbara wrote:

Hi Dave,
I found out from where the user got that file!!! txt! so I just imported
again to Excel giving all columns General formating and it worked just fine.

Thank you for your reply.
Barbara

"Dave Peterson" wrote:

First, 07-04-04 is still pretty ambiguous. Even when you say it should be April
4, 2007, does that mean that the first 04 is the month or the last 04 is the
month.

Second, this is what I'd try.

Insert a couple of extra columns to the right of the column with the dates.
In the first helper column (column B), I'd put a formula that just echoes the
value in the original column:

Assuming the dates are in column A (A1:A###)
=A1
but give this a nice unambiguous date format--like yyyy mmmm dd.
and drag down.

You may find that some of your "dates" aren't really dates--they don't react to
the formatting.

Then I'd use this formula in the second helper column (Column C):

=text(b1,"yy-mm-dd")
and drag down.

See if those text values match how you want to interpret the dates in the
original column.

If they don't match, then you'll have to fix them.

But after they're done, you can
convert to values
select column C
edit|copy
edit|paste special|values

Then (with column C) still selected,
Data|Text To columns
fixed width (but don't have any separator lines)
and choose date (ymd) for that field
and plop it back into column C.

Then format this column in an unambiguous format and check those dates to see if
they really are what they should be. Just because they're dates, doesn't mean
they'll be the dates that you expect.

==========
ps.

If these dates are being imported from a text file, it might be easier to
reimport the data and correctly specify the mdy order of each date field.


Barbara wrote:

Hi,

I have a file with a date column that appears right on the screen. That is,
07-04-04, meaning, 2007 April 4th.
The thing is when I change the date format it turns to 2004-April-07!
Is there any way to change this without having to retype all the dates??

Thanks very much for any help.
Barbara

--

Dave Peterson


--

Dave Peterson

With my Regional settings of short date M/d/yyyy 07-04-04 returns

July 4, 2004

I would check in Regional Settings to see what the short date setting is.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 06:27:03 -0800, Barbara
wrote:

Hi,

I have a file with a date column that appears right on the screen. That is,
07-04-04, meaning, 2007 April 4th.
The thing is when I change the date format it turns to 2004-April-07!
Is there any way to change this without having to retype all the dates??

Thanks very much for any help.
Barbara