+ Reply to Thread
Results 1 to 10 of 10

Date & time single cell formatting

  1. #1
    Registered User
    Join Date
    12-01-2013
    Location
    England
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Date & time single cell formatting

    Hi all,

    I am currently archiving some old SMS messages from iPhone.
    By default, the time and date of each message is recorded in one cell as "yyyy-mm-dd@hh:mm:ss", for example "2014-03-11@21:56:22".

    Is it possible to reformat this data into something more readable, such as "11th March 2014, 9:56pm"?

    Thanks in advance.

  2. #2
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    Re: Date & time single cell formatting

    format > custom > dd mmm yyyy hh:mm AM/PM
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  3. #3
    Registered User
    Join Date
    12-01-2013
    Location
    England
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: Date & time single cell formatting

    Thanks for the reply Blake but unfortunately the cells still read as "2014-03-11@21:56:22" etc.

    I would ideally like them to display in a more readable format.

  4. #4
    Forum Contributor
    Join Date
    08-10-2010
    Location
    Tavira, POrtugal
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Date & time single cell formatting

    upload a little example of the spreadsheet.

  5. #5
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    Re: Date & time single cell formatting

    can you upload a small sample of your workbook because the custom format i typed should work eg from this 11/03/2014 10:31 to this 11 Mar 2014 10:31 AM

    i have to leave now but i'm sure you will get it sorted - if not i'll look tomorrow
    Last edited by Blake 7; 03-12-2014 at 01:47 PM.

  6. #6
    Registered User
    Join Date
    12-01-2013
    Location
    England
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: Date & time single cell formatting

    Here is a sample.

    I'm using the Mac OSX version of Excel if that makes any difference.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: Date & time single cell formatting

    Formula for B4
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: Date & time single cell formatting

    if you wanted to display it like - Sunday 15th December 2013 7:16 PM

    use this formula in C4 (it requires formula in above post to be in B4, as this will convert the text in A4 to date/time)
    Please Login or Register  to view this content.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Date & time single cell formatting

    Enter this in B4 and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Select the cells in column B and format
    dd mmmm yyyy hh:mm:ss AM/PM
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    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: Date & time single cell formatting

    Maybe this one

    =TEXT(--SUBSTITUTE(A1,"@"," "),"dd ")&TEXT(--SUBSTITUTE(A1,"@"," "),"mmmm ")&YEAR(--SUBSTITUTE(A1,"@"," "))&" "&TEXT(MOD(SUBSTITUTE(A1,"@"," "),1),"h:mm AM/PM")
    Last edited by AlKey; 03-12-2014 at 03:01 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

+ 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. How to split date and time from single cell to two cells
    By taprico in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-21-2013, 12:14 PM
  2. [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
  3. [SOLVED] Excel Time formatting for multiple times in a single cell
    By Benisato in forum Excel General
    Replies: 3
    Last Post: 07-17-2012, 04:18 AM
  4. combine date and time in a single cell
    By RWB1967 in forum Excel General
    Replies: 5
    Last Post: 10-20-2011, 03:07 AM
  5. seprate date and time from single cell
    By dgtrajan in forum Excel General
    Replies: 1
    Last Post: 09-16-2009, 02:25 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