Extract date and time from a string

[pickslides]

I have this data & would like to extract the date in dd/mm/yy and the time stamp separately to use in some calcs.

Jan 10, 2014 4:47 AM
Jan 10, 2014 4:47 AM
Jan 14, 2014 4:30 PM
Jan 15, 2014 4:00 PM
Jan 16, 2014 5:01 PM

MQ

[Pete_UK]

Are those text values, or date/times formatted to look like that?

Pete

[pickslides]

They are General, when I chage to date format it stays the same.

This data came out of a CISCO telephany system

[martindwilson]

as long as jan 01 is jan 01 not jan 1
simply =DATEVALUE(MID(A1,5,2)&LEFT(A1,3)&MID(A1,9,4)) for date and
=TRIM(RIGHT(A1,8))+0 cell format as h:mm am/pm for time

[Pete_UK]

They are text values, then. Assuming the first is in cell A1, put this formula in B1:

=DATE(MID(A1,9,4),MATCH(LEFT(A1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID(A1,FIND(",",A1)-2,2))

Format as a date in the style you prefer, then copy down. Put this formula in C1:

=--(MID(A1,FIND(" ",A1,12)+1,5)&":00")+IF(ISNUMBER(FIND("PM",A1)),0.5)

Format that as a time in your preferred style, then copy down.

Hope this helps.

Pete

[pickslides]

as long as jan 01 is jan 01 not jan 1
simply =DATEVALUE(MID(A1,5,2)&LEFT(A1,3)&MID(A1,9,4)) for date and
=TRIM(RIGHT(A1,8))+0 cell format as h:mm am/pm for time

Thanks heaps, that worked nicely..

Hmmm, I have noticed that dates each month before the 10th do return a #VALUE!, do you know a work around?

[Pete_UK]

Try mine ...

Pete

[martindwilson]

that would be
=DATEVALUE(TRIM(MID(A1,FIND(",",A1)-2,2))&LEFT(A1,3)&MID(A1,FIND(",",A1)+2,4))

[daddylonglegs]

This should work for any date

=SUBSTITUTE(MID(A1,5,8),",",LEFT(A1,3))+0

format in required date format

[pickslides]

Try mine ...

Pete

Same thing happened with your solution Pete!

[pickslides]

This should work for any date

=SUBSTITUTE(MID(A1,5,8),",",LEFT(A1,3))+0

format in required date format

This one worked really well.