Results 1 to 6 of 6

Formula needed to change date format

This is a discussion on Formula needed to change date format within the Excel Questions forums, part of the Question Forums category; I have data that has been dumped to excel from SAP. Some of the cells are showing a custom date ...

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Location
    Princeton, NJ
    Posts
    97

    Default

    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!

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default

    =left(a1,2) & "-" & mid(a1,4,2) & "-" & right(a1,4)
    Does my a$$ look big in this picture ?

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Location
    Princeton, NJ
    Posts
    97

    Default

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

  4. #4
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default

    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")
    Does my a$$ look big in this picture ?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    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?

  6. #6
    Board Regular
    Join Date
    Sep 2002
    Location
    Princeton, NJ
    Posts
    97

    Default

    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.


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com