+ Reply to Thread
Results 1 to 4 of 4

Need to separate date/time from a single cell and create a date/time chart

  1. #1
    Registered User
    Join Date
    03-05-2004
    Posts
    31

    Need to separate date/time from a single cell and create a date/time chart

    Hello all

    I use excel for basic calculations and am not an advanced user hence the question. Im trying to figure out how I can separate the date and time from the column and then create a date - time chart. I'm trying to figure out which times have the most traffic.

    Any help would be great. Thanks in advance.

    Attached worksheet. time and date.xlsx

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need to separate date/time from a single cell and create a date/time chart

    excel stores the date & time as a number with decimals. the date is the whole number and the time is stored in the decimal part. To obtain the date only use the =trunc() formula to obtain the time just substract the original value from the truncated value

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,135

    Re: Need to separate date/time from a single cell and create a date/time chart

    In one cell use = INT(A1) for date and =MOD(A1,1) for time - Format as needed
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  4. #4
    Registered User
    Join Date
    03-05-2004
    Posts
    31

    Re: Need to separate date/time from a single cell and create a date/time chart

    Thanks for the replies. I'm missing something or doing something wrong. I used "= INT(A1) for date and =MOD(A1,1)" 20-06-2014 07-13-51.png

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,135

    Re: Need to separate date/time from a single cell and create a date/time chart

    In your image date and time are gone! You just need to format the cells correctly say dd-mm-yyyy for date and hh:mm:ss for time

+ 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. Date & time single cell formatting
    By djg1991 in forum Excel General
    Replies: 9
    Last Post: 03-12-2014, 02:31 PM
  2. Trying to create chart with multiple time entries for same date
    By blandrigan in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-15-2012, 12:01 AM
  3. [SOLVED] Combine Date and Time into one single Cell
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2012, 03:55 PM
  4. seprate date and time from single cell
    By dgtrajan in forum Excel General
    Replies: 1
    Last Post: 09-16-2009, 02:25 PM
  5. Replies: 0
    Last Post: 08-23-2005, 12:23 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