+ Reply to Thread
Results 1 to 8 of 8

How do I separate the date and time in this column?

  1. #1
    Registered User
    Join Date
    11-16-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    28

    How do I separate the date and time in this column?

    Hi,
    I have a data set that I have pulled this column from and I am struggling to separate the date from the time in a clean way.

    When I use the text to column method some of the dates still have a 00:00 time stamp on them towards the bottom.

    Does anyone know the best way to separate the data and time in the sheet provided.

    Thanks
    Attached Files Attached Files

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

    Re: How do I separate the date and time in this column?

    You have some date/times as text values, and some as numeric values, though the ones that are numeric have the month and day transformed. You can use this formula to get the date (e.g. in B2):

    =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)))

    and this one in C2 to get the time:

    =IF(ISNUMBER(A2),MOD(A2,1),--MID(A2,FIND(" ",A2)+1,10))

    Format C2 as a time in the style you prefer, then copy both formulae down to the bottom of your data.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How do I separate the date and time in this column?

    Simply format the date column in the format that you want, for example Short Date or Custom m/dd/yyyy .

    The "problem" is: near the end of table (maybe also in the middle), the data is numeric, not text. When you see the separated date as 9/30/2019 00:00, it simply means that the cell contains only a date (or time is truly midnight). A date is simply an integer; time is simply a fraction of 1 (day). If the fraction is zero, it looks like a time of 00:00 (midnight).

    Apparently, Text To Columns is smart enough to use a date-only format when the data is text; but not when the data is numeric.

    PS.... It is unclear why some of the data is text, in the first place. If your regional date form is d/m/y or anything else other than m/d/y, even TTC might not recognize and format the date correctly for your region unless you choose MDY in the final dialog box where the Finish button is.
    Last edited by joeu2004; 10-01-2019 at 12:13 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How do I separate the date and time in this column?

    For Date

    Enter formula in B2 and copy down

    =INT(A2) Format as Date: mm/dd/yyyy
    For Time

    Enter formula in C2 and copy down

    =MOD(A2,1) Format as Time: hh:mm:ss
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    11-16-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    28

    Re: How do I separate the date and time in this column?

    Quote Originally Posted by AlKey View Post
    For Date

    Enter formula in B2 and copy down

    =INT(A2) Format as Date: mm/dd/yyyy
    For Time

    Enter formula in C2 and copy down

    =MOD(A2,1) Format as Time: hh:mm:ss
    Hi I have given this a go and it doesn't seem to have worked. I have attached a file showing how I have used the formulaes you have provided.

    Please find attached.

    Thanks

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

    Re: How do I separate the date and time in this column?

    Did you try my formulae from Post #2 - they worked in all cases when I tried them yesterday.

    Pete

  7. #7
    Registered User
    Join Date
    11-16-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    28

    Re: How do I separate the date and time in this column?

    Quote Originally Posted by Pete_UK View Post
    Did you try my formulae from Post #2 - they worked in all cases when I tried them yesterday.

    Pete
    hi Pete, yes they did. Both worked fine. Much appreciated I just had to close and open a new sheet for some reason.

    thanks again

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

    Re: How do I separate the date and time in this column?

    Glad to hear it, and thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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. Parse a date+time field into separate date and time cells
    By meowmeow22 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2019, 10:37 AM
  2. [SOLVED] combine separate date and time columns into a single column
    By jbporcher in forum Excel General
    Replies: 7
    Last Post: 05-17-2018, 12:50 PM
  3. Replies: 7
    Last Post: 06-19-2017, 06:10 PM
  4. Replies: 2
    Last Post: 07-05-2016, 03:49 PM
  5. Replies: 2
    Last Post: 03-24-2016, 11:49 PM
  6. How to separate given date and time
    By srinivasd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-13-2015, 12:31 AM
  7. 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

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