I have a cell that is in date format. However it is in the wrong format. What
shows as 7/11/2001 really is supposed to be 2007/11/01. What shows as
7/12/2005 really should be 2007/12/05. Is there a formula I can use to
convert these. I dont care if the result is a date, text or numeric
formattted cell so long as 7/11/2001 reads as 2007/11/01 (11/01/2007) would
be acceptable as well.

Thanks

hi
custom fomat maybe
right click cell with date. click format cells.click number tab.
in the catagory window, click custom. in the type box enter....
yyyy/dd/mm
click ok.
you can now format any cell with this format.

regards
FSt1


"GKW in GA" wrote:

I have a cell that is in date format. However it is in the wrong format. What
shows as 7/11/2001 really is supposed to be 2007/11/01. What shows as
7/12/2005 really should be 2007/12/05. Is there a formula I can use to
convert these. I dont care if the result is a date, text or numeric
formattted cell so long as 7/11/2001 reads as 2007/11/01 (11/01/2007) would
be acceptable as well.

Thanks

What is it that you entered into this cell in the first place?

Rick


"GKW in GA" wrote in message
...
I have a cell that is in date format. However it is in the wrong format.
What
shows as 7/11/2001 really is supposed to be 2007/11/01. What shows as
7/12/2005 really should be 2007/12/05. Is there a formula I can use to
convert these. I dont care if the result is a date, text or numeric
formattted cell so long as 7/11/2001 reads as 2007/11/01 (11/01/2007)
would
be acceptable as well.

Thanks

Yeah, I'm with Rick; the problem here isn't that the format is misleading but
(judging by your description) that the spreadsheet misunderstood the dates as
they were entered. To put it another way, Excel isn't displaying the dates
in a way that looks confusing to you; it really thinks that that first date
is supposed to be July 11, 2001, and the second July 12, 2005. Re-enter
those dates correctly and you'll have no difficulty getting them to display
in whatever format you choose.

--- "Rick Rothstein (MVP - VB)" wrote:
What is it that you entered into this cell in the first place?

"GKW in GA" wrote in message
...
I have a cell that is in date format. However it is in the wrong format.
What shows as 7/11/2001 really is supposed to be 2007/11/01. What
shows as 7/12/2005 really should be 2007/12/05. Is there a formula I
can use to convert these. I dont care if the result is a date, text or
numeric formattted cell so long as 7/11/2001 reads as 2007/11/01
(11/01/2007) would be acceptable as well.

yes, I agree, they were entered incorrectly in the first place. THe problem
is they were not keyed in, they were loaded from another application where
they were formatted incorrectly before being imported to Excel. Problem is
there are several thousand of them.

That is why I was looking for a formula.

"Bob Bridges" wrote:

Yeah, I'm with Rick; the problem here isn't that the format is misleading but
(judging by your description) that the spreadsheet misunderstood the dates as
they were entered. To put it another way, Excel isn't displaying the dates
in a way that looks confusing to you; it really thinks that that first date
is supposed to be July 11, 2001, and the second July 12, 2005. Re-enter
those dates correctly and you'll have no difficulty getting them to display
in whatever format you choose.

--- "Rick Rothstein (MVP - VB)" wrote:
What is it that you entered into this cell in the first place?

"GKW in GA" wrote in message
...
I have a cell that is in date format. However it is in the wrong format.
What shows as 7/11/2001 really is supposed to be 2007/11/01. What
shows as 7/12/2005 really should be 2007/12/05. Is there a formula I
can use to convert these. I dont care if the result is a date, text or
numeric formattted cell so long as 7/11/2001 reads as 2007/11/01
(11/01/2007) would be acceptable as well.

I believe I have come up with a formula that will do the trick:
=CONCATENATE("20",TEXT(MONTH(C12),"00"),"/"DAY(C12),"/",RIGHT(YEAR(C12),2))

This will convert 7/11/2001 to 2007/11/01


"GKW in GA" wrote:

yes, I agree, they were entered incorrectly in the first place. THe problem
is they were not keyed in, they were loaded from another application where
they were formatted incorrectly before being imported to Excel. Problem is
there are several thousand of them.

That is why I was looking for a formula.

"Bob Bridges" wrote:

Yeah, I'm with Rick; the problem here isn't that the format is misleading but
(judging by your description) that the spreadsheet misunderstood the dates as
they were entered. To put it another way, Excel isn't displaying the dates
in a way that looks confusing to you; it really thinks that that first date
is supposed to be July 11, 2001, and the second July 12, 2005. Re-enter
those dates correctly and you'll have no difficulty getting them to display
in whatever format you choose.

--- "Rick Rothstein (MVP - VB)" wrote:
What is it that you entered into this cell in the first place?

"GKW in GA" wrote in message
...
I have a cell that is in date format. However it is in the wrong format.
What shows as 7/11/2001 really is supposed to be 2007/11/01. What
shows as 7/12/2005 really should be 2007/12/05. Is there a formula I
can use to convert these. I dont care if the result is a date, text or
numeric formattted cell so long as 7/11/2001 reads as 2007/11/01
(11/01/2007) would be acceptable as well.

A couple of things...

First, your proposed formula does not return a date, it returns text that
happens to look like a date... you will not be able to do some date
manipulations with it. If you put a double unary (two minus signs) in front
of it, that will convert it to a date; although, depending on your system
settings, it may not be the correct date (your presentation of the year
first, followed by what I assume is the month, then day may confuse the
conversion).

This brings me to the second "problem"... it is impossible to tell from your
example dates which number is the month and which is the day (it is usually
best with example dates to use a day number greater than 12). Assuming for
the incorrectly entered date of 7/11/2001, that the year is 2007, the month
is 11 and the day is 1, use this formula...

=DATE(MONTH(A1),DAY(A1),YEAR(A1)-2000)

which will return a real date (or date serial number depending on the cell
format)... just reset the cell format to the Custom format of yyyy/mm/dd and
it will remain a real date, but look the way you want.

Rick


"GKW in GA" wrote in message
...
I believe I have come up with a formula that will do the trick:
=CONCATENATE("20",TEXT(MONTH(C12),"00"),"/"DAY(C12),"/",RIGHT(YEAR(C12),2))

This will convert 7/11/2001 to 2007/11/01


"GKW in GA" wrote:

yes, I agree, they were entered incorrectly in the first place. THe
problem
is they were not keyed in, they were loaded from another application
where
they were formatted incorrectly before being imported to Excel. Problem
is
there are several thousand of them.

That is why I was looking for a formula.

"Bob Bridges" wrote:

Yeah, I'm with Rick; the problem here isn't that the format is
misleading but
(judging by your description) that the spreadsheet misunderstood the
dates as
they were entered. To put it another way, Excel isn't displaying the
dates
in a way that looks confusing to you; it really thinks that that first
date
is supposed to be July 11, 2001, and the second July 12, 2005.
Re-enter
those dates correctly and you'll have no difficulty getting them to
display
in whatever format you choose.

--- "Rick Rothstein (MVP - VB)" wrote:
What is it that you entered into this cell in the first place?

"GKW in GA" wrote in message
...
I have a cell that is in date format. However it is in the wrong
format.
What shows as 7/11/2001 really is supposed to be 2007/11/01. What
shows as 7/12/2005 really should be 2007/12/05. Is there a formula
I
can use to convert these. I dont care if the result is a date, text
or
numeric formattted cell so long as 7/11/2001 reads as 2007/11/01
(11/01/2007) would be acceptable as well.