Results 1 to 7 of 7
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Parsing data in a cell (2003)

    I have a field that contains a date and a time in the following format: 11/29/2003 18:29. Is there a function or a macro through which I can parse the data so that the date remains in the cell as indicated and the time is moved to a new field, adjacent to the date? I'd like to be able to import this data in an Access database, and Access doesn't like the date and time in one field. Thanks!
    JimmyW
    Helena, MT

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Parsing data in a cell (2003)

    if date/time is in A1:
    In B1 enter:
    <pre>=int(A1)</pre>


    and in C1 enter:
    <pre>=mod(A1,1)</pre>


    Copy these down as many rows as needed then copy these 2 columns and paste special- values. Then you can delete col A and you will have parsed the date/time into date and time

    Steve

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Parsing data in a cell (2003)

    Thanks. I tried your suggestion, but this is what resulted in each column: A=11/29/2003 18:29, B=11/29/2003 0:00, C=1/0/1900 18:29 So, Column B contained the correct date and C contained the correct time, but each also contained a date/time. (A was the original.)
    JimmyW
    Helena, MT

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Parsing data in a cell (2003)

    Try changing the cell format for each column to show either just the date or just the time.

    zeddy

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing data in a cell (2003)

    Those results are correct. Excel keeps all dates and all times as date/time values. A date entered without a time is kept as 12 midnight (0:00) of that date. A time entered without a date is kept as that time in the first day in Excel's date/time range (the 0th day of 1900 which is actually the last day of 1899). Change the format of column B to display just the date and Column C to display just the time.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Parsing data in a cell (2003)

    PMFJI so late, but Access has a date and time combined field type, for example "General Date 1/1/2004 5:30:23 PM", and so should accept an import from an Excel Date & Time Cell. Have you tried altering the Excel Date & Time Cell formats to "mm/dd/yyyy hh:mm:ss AM/PM" or other field type native to Access? (I'm assuming US format dates here.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Parsing data in a cell (2003)

    Thanks, John, and everyone else. The Excel cells in question are formatted as Custom, m/d/yyyy h:mm. Access would not accept that format. But, I changed the Excel format to Date, m/dd/yy hh:mm, and Access was okay with that format. When importing from Excel, Access does not offer the option to change the field type. If I try to just import the tab delimited text file into Access, bypassing Excel, Access formats the field as date/time, but offers no option to choose what type of date/time format, and fails to import the data. So, perhaps the best approach is to reformat the Excel cells as noted above.
    JimmyW
    Helena, MT

Posting Permissions

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