+ Reply to Thread
Results 1 to 3 of 3

Splitting a time stamp into one column for time and another for date

  1. #1
    Registered User
    Join Date
    09-19-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    2

    Splitting a time stamp into one column for time and another for date

    Hello,
    I am having a hard time splitting this time stamp into two different columns. When I try to use the built in function it splits it into three columns: one for date, one for time, and another for AM/PM. After it does this it converts all of the times to AM. I've tried formatting the columns in advance but the GUI for splitting doesn't give me too many options as far as conversion. The original times are all in world(military) time, but excel converts them into standard(U.S.) time. I've tried writing a formula based on other threads I've seen concerning splitting cells but I end up with a fraction of a number, even when I try to tell it to output the data as a time or date. Any help would be much appreciated.
    9/1/2013 19:00 this is what the cells look like but they convert to this 9/1/2013 7:00:00 PM when they are double clicked

    Thanks in advance,
    Ellsworth

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Splitting a time stamp into one column for time and another for date

    welcome to the forum, Ellsworth. ending up with a fraction of a number is correct for time. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).*

    so simply use:
    =INT(A1)
    =MOD(A1,1)
    and format it to whatever format you require. if it doesn't help, attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    09-19-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Splitting a time stamp into one column for time and another for date

    Yeah, that's perfect and makes me feel pretty stupid not knowing how to do something so simple.
    Thanks a bunch

+ 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. Adding time (more than 24 hours) to date time stamp
    By sidapt in forum Excel General
    Replies: 6
    Last Post: 01-06-2014, 04:08 AM
  2. [SOLVED] Extract a time from a cell with date and time stamp and then perform calculations
    By Marcos Aristotelous in forum Excel General
    Replies: 3
    Last Post: 10-31-2012, 04:36 AM
  3. Remove Time on Date/Time Stamp
    By pinoydarv in forum Excel General
    Replies: 8
    Last Post: 11-02-2011, 08:47 AM
  4. Date and Time stamp column when row updated
    By batman1056 in forum Excel General
    Replies: 6
    Last Post: 02-11-2011, 07:10 AM
  5. Splitting Date & Time Into 24 Hour Time
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-05-2008, 05:26 AM

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