1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Date "Reversed" Problem in Excel

Discussion in 'Business Applications' started by Gogs, Jan 24, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    Hi Guys,

    I've imported a CSV file into excel. However one of the columns contains dates which have been exported by the original bespoke software as yyyymmdd or at least in that style (theres no formating applied, despite me saying it was a date field when importing) and it won't let me apply any formatting at all.

    Is there an easy way I can "reverse" how its displayed so it I can format it to a ddmmyyyy date? I've tried using MID to extract the characters in the reuqired order but still can't apply any formatting to it. It just comes up as #########. I need it in a recognised date format as I require to do some date orientated calculations after.

    thanks in advance

    Gogs
     
  2. coachdan32

    coachdan32

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Have you tried =text(mm/dd/yyyy)
     
  3. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    Assuming your 8 digit number (date) is in cell A6, try:

    =DATE(LEFT(A6,4),MID(A6,5,2),RIGHT(A6,2))

    Then just set the cell format to the appropriate date format you want displayed.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Are you sure that the column is not too narrow? Excel often displays these characters in the Column when it is not wide enough to display the contents. Try autofitting the column or manually making it wider.

    Rollin
     
  5. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    Thanks for the replies guys.

    I used ddockstader's method as the =Text(A6,mm/dd/yyyy) didn't seem to work. Don't know why, it seemed plausable enough.

    Thanks once again

    Gogs
     
  6. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    If the imported records are set as text (commonly the case) then multiplying the cells by 1 often allows susequent formatting as it overrides the set imported formats.

    in a spare cell, enter 1
    Select the cell, and Edit - Copy
    Select the date cells and Edit -Past Special - select "values" and "multiply" and OK

    Then try to reformat or any of the above cures.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/322943

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice