+ Reply to Thread
Results 1 to 6 of 6

Dealing with different types of date data in the same field

  1. #1
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Dealing with different types of date data in the same field

    In a certain column I have

    1/02/2014 8:43:26 AM
    1/02/2014 8:43:29 AM
    1/13/14 8:46:38 AM
    1/13/14 8:46:40 AM
    1/13/14 9:00:00 AM


    I want to extract the date, =int(a2) works for the first two items but then returns #VALUE! for the following three.

    Also these dates are in mm/dd/yy, is there a way to make them dd/mm/yy?

    MQ
    Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Dealing with different types of date data in the same field

    Attach a sample Excel workbook so we can see how the data appears with our regional settings.

    Pete

  3. #3
    Forum Contributor
    Join Date
    07-29-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    365
    Posts
    255

    Re: Dealing with different types of date data in the same field

    Just been trying to do this, file wont attach.. Will try later on a different machine.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dealing with different types of date data in the same field

    at a guess you have got that info from somewhere using the US date system the first 2 were recognised as dates the last 3 wont resolve as there is no month 13 also the first 2 are probably wrong and should be 2 jan 2014 but excel has seen then as 1 feb
    usually i just change my regional settings to us date,import the data then change back to uk or in your case aus
    Last edited by martindwilson; 02-19-2014 at 11:37 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Dealing with different types of date data in the same field

    to answer your first question
    reason why the first two works is because 1st of 2nd is plausible date, while 1st of 13th month is not

    though i would think int() does not give you want you want anyway as it still gives the MM/DD/YYYY version

    simply way to flip the DD/MM is to text to column the column and use the MDY instead of DMY
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dealing with different types of date data in the same field

    to use text to columns you need to
    1 select the column and find space replace with 5 spaces (this is to allow the data to line up in text to columns view else dates like 31 and times like 12 will skew things)
    2. select data /text to columns/fixed width click next
    3 double click on the line between times and am/pm to remove it click next
    4. the first field should be high lighted and marked as general if it isnt click to select it
    5 now change the first field to date by checking the button and select MDY from the drop down c
    click finish
    format the columns date and time in formats of your choice

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 02-22-2013, 02:35 AM
  2. Extract a date from a field and append another field's data
    By Leroy221 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-07-2012, 11:35 AM
  3. Replies: 3
    Last Post: 05-28-2008, 01:32 PM
  4. Pivot table field types
    By stevehere in forum Excel General
    Replies: 2
    Last Post: 06-01-2005, 06:54 AM
  5. Replies: 1
    Last Post: 04-01-2005, 08:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1