Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi there
    I have exported a large database into excel.
    There are several columns with dates and what ever I do they stay the same - for instance 1/10/2009 will never be 01/10/2009.
    Because I use these dates with a concatenate function and then a " | " it would look a lot neater if the " | " are all in one line.
    1/10/2009 | test 1
    2/05/2009 | test 2
    12/05/2009 | test 3

    How can I change the format so it always shows dd mm yyyy - believe me I have just about tried everything.

    Thanks heaps

  2. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Try this =CONCATENATE(TEXT(A1,"dd/mm/yyyy")," | ",B1)

    Where the date is in cell A1 and the Test No is in cell B1

    See attached. As you can see in column C the result is what you appear to want.
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If your dates don't change when you alter the format, then they are stored as text. To convert them, select them, choose Data-Text to columns, step through the dialog until step 3 when you need to specify it's a date field and the order (DMY for example) then press Finish.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks heaps
    That worked just fine

    Cheers

Posting Permissions

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