Results 1 to 11 of 11

Thread: Date and Time

  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,120
    Thanks
    40
    Thanked 13 Times in 11 Posts

    Date and Time

    I have a spreadsheet that in Column A shows the following: 08/10/2015 7:34. This is a Save As from an app that I use. Is there a way to separate out the date and time into two different columns. I also have the info in a .csv file.

    Thanks
    John
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,872
    Thanks
    22
    Thanked 545 Times in 516 Posts
    The answer is, it depends.
    If the data is a number formatted as date you can just change the format.
    If it is text you can use Left and Right or Mid, but it depends on what you want the result to be, Date or text.

    Assuming it's text to Date then use these two formula: =DATEVALUE(A1) and =TIMEVALUE(A1)

    cheers, Paul

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,120
    Thanks
    40
    Thanked 13 Times in 11 Posts
    Thanks Paul those turned up #VALUE!

    Date Time Test.xls
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    7,871
    Thanks
    280
    Thanked 1,117 Times in 1,022 Posts
    John,

    These work for me:

    Date: =DATE(YEAR(A1),MONTH(A1),DAY(A1))
    Time: =TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
    JohnSplitDate.JPG

    If these don't work for you please post some sample data in a workbook.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,872
    Thanks
    22
    Thanked 545 Times in 516 Posts
    That's because they are already Dates (numbers formatted as Date). All you need to do is change the cell format.
    If you want them across two cells you can either copy or use "=A1" and format as Time.

    cheers, Paul

  6. The Following User Says Thank You to Paul T For This Useful Post:

    t8ntlikly (2015-08-27)

  7. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,120
    Thanks
    40
    Thanked 13 Times in 11 Posts
    Thanks RG and Paul that is what I was looking for
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  8. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,386
    Thanks
    257
    Thanked 238 Times in 229 Posts
    There is another way to do this, which for me is very straightforward: Text to Columns.

    This is how you do it in Excel 2007 and 2010.

    Make sure that there is a blank column to the immediate right of the column where your data is.
    Highlight the column with the date and time, and choose Data / Text to Columns.
    Choose "Delimited" rather than "Fixed Width", and select Space as your delimiter.
    Scroll through the preview list, to make sure that it will separate correctly. Click Next when done.
    Highlight each column on the next preview screen, and choose the correct format for each (date, text, etc).
    Click Finish, and you're done.

  9. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    5,872
    Thanks
    22
    Thanked 545 Times in 516 Posts
    Will that work if the data is Dates rather than text?

    cheers, Paul

  10. #9
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,386
    Thanks
    257
    Thanked 238 Times in 229 Posts
    Quote Originally Posted by Paul T View Post
    Will that work if the data is Dates rather than text?

    cheers, Paul
    No; the target column has to be text.

    Another thing is that it adds "AM" to the value in the original cell, and then attempts to separate it into three columns: 8/10/2015, 7:00:00, and AM.

    You can do Fixed Width rather than Delimited, if all of the data lines up. By doing Fixed Width, you can include the AM (or PM), and you can trim out the blank space between the columns. But Fixed Width leaves "00.00" in the left (date) column, as well as separating "7:00:00 AM" into to the right (time) column.

    Text to Columns is a powerful tool for many situations, but due to formatting concerns, RG's method (post #4) would provide the most accurate results.

    But for quick-and-dirty processing, Text to Columns is unmatched.

  11. #10
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,991
    Thanks
    90
    Thanked 455 Times in 415 Posts
    And of course, the vba approach with a UDF. It doesn't matter what format column A is.

    Enter the following formulas:

    Cell F1: =DateF(A1)
    Cell G1: =TimeF(A1)

    Then copy down

    In a standard module:
    Code:
    Public Function TimeF(rng As Range)
        TimeF = Format(rng, "h:mm")
    End Function
    
    Public Function DateF(rng As Range)
        DateF = Format(rng, "mm/dd/yyyy")
    End Function
    dt1.png

    dt2.png

    HTH,
    Maud

  12. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Maudibe View Post
    And of course, the vba approach with a UDF. It doesn't matter what format column A is.

    Enter the following formulas:

    Cell F1: =DateF(A1)
    Cell G1: =TimeF(A1)

    Then copy down

    HTH,
    Maud
    Why not just use built-in functions:

    F1: = TEXT(A1, "MM/DD/YYYY")
    G1: =TEXT(A1, "H:MM")

    etc?

Posting Permissions

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