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.
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/
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.
upload a little example of the spreadsheet.
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.
Here is a sample.
I'm using the Mac OSX version of Excel if that makes any difference.
Formula for B4
Please Login or Register to view this content.
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.
Enter this in B4 and copy down.
Formula:
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks