Date in wrong format

[GKW in GA]

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

 

[FSt1]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Bob Bridges]

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.

 

[GKW in GA]

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.

 

[GKW in GA]

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

 

[Rick Rothstein \(MVP - VB\)]

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