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.

Exported date field not recognized as date in Excel

Discussion in 'Business Applications' started by DanRJFrost, Oct 11, 2013.

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

    DanRJFrost Thread Starter

    Joined:
    Dec 6, 2011
    Messages:
    47
    Hello,

    I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

    1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
    2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

    I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

    Can anyone help, I've attached a small sample of the data.

    Thanks

    Daniel
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Where does the data come from?
    What do you get if you have no cell format?
     
  3. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    The Text to Columns does work under these conditions;

    Use "Fixed Width" as the parsing condition
    where you have Times you will see 2 columns
    Click in the time column and click on "Do not import" if you don't care about the time
    Click in the date Column and set it to Date: MDY
    Click Finish

    By setting the date to MDY you are telling Excel how the input data is configured and it will then translate it to your default.

    In the future when you import the data you should paste it into a text or csv file and "Import" it using the same rules as above. If that is not feasible you can always set up a simple macro to do what I described above.
     
  4. DanRJFrost

    DanRJFrost Thread Starter

    Joined:
    Dec 6, 2011
    Messages:
    47
    Hi

    Thanks Xcubed that has worked brilliantly.

    The data is produced by the system on a daily basis, I'm speaking with the tech team who created it to see if they can change this.

    Much much appreciated.

    Tech Support Guy Forums does it again!

    Dan
     
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/1110471

  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