Formula needed to change date format

[KirstenNJ]

I have data that has been dumped to excel from SAP. Some of the cells are showing a custom date format of mm/dd/yyyy (example: 01/01/2002). The data really should really be text and be 01-01-02. It isn't a date at all. I can make it look like 01-01-02 as a date but when I try to change it to text it makes it a 5 digit number. Anyone know the formula to convert to the format I want?
Thanks!

 

[DonkeyOte]

=left(a1,2) & "-" & mid(a1,4,2) & "-" & right(a1,4)

 

[KirstenNJ]

That doesn't work either since it still shows it as the funky excel 5 digit (or so) date format.

 

[DonkeyOte]

maybe I don't understand the problem then...

if it's a date field and you want it to be a text field then you want

=text(a1,"dd-mm-yy")

 

[Aladin Akyurek]

On 2002-10-11 11:52, KirstenNJ wrote:
I have data that has been dumped to excel from SAP. Some of the cells are showing a custom date format of mm/dd/yyyy (example: 01/01/2002). The data really should really be text and be 01-01-02. It isn't a date at all. I can make it look like 01-01-02 as a date but when I try to change it to text it makes it a 5 digit number. Anyone know the formula to convert to the format I want?
Thanks!

Apply the following formulas to a cell with the troublesome 01/01/2002 in it:

=LEN(A1)

=ISNUMBER(A1)

What results do you get?

 

[KirstenNJ]

Thanks Boardmaster, the TEXT solution worked. It just didn't like if I picked "format, cells, text" from the menu bar but writing the formula in a separate cell works.