+ Reply to Thread
Results 1 to 7 of 7

Parse a date+time field into separate date and time cells

  1. #1
    Registered User
    Join Date
    07-26-2019
    Location
    NY
    MS-Off Ver
    1906, latest
    Posts
    19

    Lightbulb Parse a date+time field into separate date and time cells

    Hi team,

    Thank you for your help in the past. I would really appreciate some insight here as I have been exporting from a system which provides date and time like this, all in one cell:

    Please Login or Register  to view this content.
    My end result would be

    Unstructured Date
    6/10/2019 9:21

    -->>>

    Date
    6/10/2019

    Time
    9:21

    As you can see, a simple =Mid or =Find function may not work, because the Month could be 6 or 10, for June or October, and the time could be 17:00 or 9:00.

    Please advise! (attached a csv as well)

    Thank you again,
    Meow
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: Parse a date+time field into separate date and time cells

    Welcome to the forum.

    In C2:

    =A2

    formatted as short date (on the Home Ribbon - drop-down)

    In D2:

    =A2

    formatted as time (on the Home Ribbon - drop-down)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,228

    Re: Parse a date+time field into separate date and time cells

    Insert two columns to the right of the current data.

    In B1 =INT('date fixer'!$A2) formatted as a short date and copy down

    in C1 ='date fixer'!$A2-INT('date fixer'!$B2) formatted as Time and copied down

    Do the same for the former column B

  4. #4
    Registered User
    Join Date
    12-10-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    48

    Re: Parse a date+time field into separate date and time cells

    I know its a manual way, but can you find and replace your space between your time and date and replace it with say, 4 spaces, then do a data split?

    so you find (without quotations) " " and replace with "5 spaces" (no quotations again and it was only showing one in my post, but 5 spaces not the words) this should space things out for you, then...

    Data tab - Text to Column - Split fixed width spaces - insert a break after your date length (2-3 spaces in should do the trick this way your date and time are not affected by the split)

    And insert in to new column.

    Didn't see alan's post that may be a better way to go about it...

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Parse a date+time field into separate date and time cells

    In Excel Datetime is expressed as DateValue.TimeValue.

    Meaning 1 = 1 day = 24 hours. 1/24 = 1 hour.

    So...
    =INT(Datetime)
    Will return date value.
    =MOD(Datetime, 1)
    Will return time value.

    Format each cell as Date & time respectively.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  6. #6
    Registered User
    Join Date
    07-26-2019
    Location
    NY
    MS-Off Ver
    1906, latest
    Posts
    19

    Re: Parse a date+time field into separate date and time cells

    Thanks all.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,228

    Re: Parse a date+time field into separate date and time cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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: 7
    Last Post: 06-19-2017, 06:10 PM
  2. Replies: 2
    Last Post: 03-24-2016, 11:49 PM
  3. Need to separate date/time from a single cell and create a date/time chart
    By slicksilver79 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-19-2014, 09:48 PM
  4. How to separate date and time into different cells
    By MDNYC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 12:10 PM
  5. [SOLVED] Extracted time from date/time field does not flag on =IF(TIME function.
    By Vlad717 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2013, 03:29 PM
  6. Replies: 1
    Last Post: 12-08-2010, 09:59 AM
  7. parse date from date/time column
    By jchambers00 in forum Excel General
    Replies: 1
    Last Post: 07-27-2009, 02:47 PM

Tags for this Thread

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