• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split date and time

mf1

mf1

Member
Hi Everyone,

I need to write a formula to split out the date and time that appear in the same cell from a data dump, i.e.
10th September 12:00 pm
4th June 6:00 pm

I can use the left or right function if it was the same month, however, different months appear and I'm not sure how to combat that.

Thanks in advance.

Narof
 

Attachments

mf1, in your attachment, the field you want to split out is not a true date/time field, it is just text... do you need the results just split as text or split and converted to a real date and real time ??
 
Date: =CONCATENATE(MID(A3,FIND(" ",A3)+1,FIND(" ",A3,FIND(" ",A3)+1)-FIND(" ",A3))," ",LEFT(A3,FIND(" ",A3)-3),", ",2015)

^^ Set Cell format to Long Date

I'm working on the time now...
 
To convert these to real Dates and Times I would convert these using:

B2: =DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)&"2015")+(LEFT(A2,FIND(" ",A2)-3)-1)

C2: =TIME(MID(A2,FIND(":",A2)-2,2),MID(A2,FIND(":",A2)+1,2),0)+IF(MID(A2,LEN(A2)-1,1)="P",IF(MID(A2,FIND(":",A2)-2,2)="12",0,0.5),0)

Copy down
 
Paul's solution returns a string and so can't be used as a real date
ie: you can't add 1 to it to get the next day
 
For Date

Either

=DATEVALUE(REPLACE(TRIM(LEFT(A2,FIND(":",A2)-3)),1,FIND(" ",A2)-1,LEFT(A2,FIND(" ",A2)-3)))

or

=REPLACE(TRIM(LEFT(A2,FIND(":",A2)-3)),1,FIND(" ",A2)-1,LEFT(A2,FIND(" ",A2)-3))+0
 
You must log in or register to reply here.
Back
Top