+ Reply to Thread
Results 1 to 5 of 5

dates and time formatting issues

  1. #1
    Registered User
    Join Date
    01-27-2012
    Location
    Berkeley, California
    MS-Off Ver
    Excel 2016
    Posts
    30

    dates and time formatting issues

    I have a column with hundreds of dates and times that look like this (year, month, day and then time):

    2012-03-28 12:03:36
    2012-03-28 12:35:02
    and so on

    so its the year, month, day and then the time.

    I cant get Excel to recognize this as a date and time so I can sort the data currently its newest to oldest so I want to reverse the order and am trying to use the data-sort command but Excel doesn't seem to recognize it as a date and time. Is there a way to get Excel to recognize this as a date and time?

    thanks, Roger

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: dates and time formatting issues

    maybe something else is involved, hidden spaces or something else for example. I just typed your two examples into excel the way they appear here and it recognized them as dates and time. Perhaps a sample spreadsheet would help?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

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

    Re: dates and time formatting issues

    Highlight the column with those date/times in (they are probably identified as text values), click on Data / Text-to-columns, click next on the first 2 panels and on the third panel select type as Date and specify YMD, then click Finish.

    Hope this helps.

    Pete

  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: dates and time formatting issues

    Or try to convert them with formula

    =INT(A1)+MOD(A1,1)

    Format cell Custom: m/d/yyyy h:mm:ss AM/PM

    Row\Col
    A
    B
    1
    2012-03-28 12:03:36
    3/28/2012 12:03:36 PM
    2
    2012-03-28 12:35:02
    3/28/2012 12:35 PM
    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
    01-27-2012
    Location
    Berkeley, California
    MS-Off Ver
    Excel 2016
    Posts
    30

    Re: dates and time formatting issues

    Thanks Pete_UK, that seemed to work.

    May I ask one more followup question:

    When I used the data text to columns tool, it worked great and but now I have the time in a column as hours:minutes:seconds AM/PM. However, the program I want to import the data into from Excel seems to only want it as hours and minutes. Is there a way to round the seconds up or down to the nearest minute and just have hours and minutes?

    thanks, Roger

+ 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. [SOLVED] time date formatting issues
    By Davis.Gray in forum Excel General
    Replies: 7
    Last Post: 08-06-2013, 04:25 PM
  2. Replies: 0
    Last Post: 02-28-2013, 05:06 AM
  3. Dates issues
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 12-13-2012, 12:44 PM
  4. Conditional formatting for dates/time x hours passed
    By Sirodot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2012, 11:04 AM
  5. Issues with dates
    By JS in forum Excel General
    Replies: 1
    Last Post: 08-03-2006, 12:15 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