Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009

    Unanswered: Date format problems in excel

    I have an excel sheet that was imported via txt file but I do not have a copy of the txt file and our programmer is out this week. I hope someone can assist. Currently it is listed as DD MM YY (with spaces) and I need it to convert to dd-mon-yy.

    Any assistance is much appreciated!


  2. #2
    Join Date
    Jul 2007
    The solution all depends on how the date is currently formatted. It sounds like it is there as text and I am assuming that 1st February 2009 would be shown as 01 02 2009.

    That being the case, you will have to re-assemble the pieces to make a date that Excel recognises and then you can use normal Excel formatting to get it to display however you want.

    The way I would do this is to put a formula into an empty column which looked something like this:

    =Date(Right(Trim(Nx),4), Mid(Trim(Nx),4,2), Left(Trim(Nx),2))

    where Nx is the location of your data (N being the column and x being the row). The Trim function is used to remove any unwanted spaces that are there but you cannot see and might otherwise much this up.

    Now you should have a date and you can format the cell (or whole column) using the Format function and choosing the Number tab - there are some defaults available for Date, but if you cannot find the one you want then choose Custom and type in dd-mmm-yy.

    Now you can shuffle your columns around and hide the original.

    Hope this all helps.


  3. #3
    Join Date
    Aug 2009
    Caspar you are my new best friend! It worked perfectly. Thank you so much!

Posting Permissions

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