Extract Date and Time from a string

[srrboarder]

Hello,
I'm working with a data export that is throwing a time stamp entry into 1 cell. I need to break out the date and time into separate cells so that excel will recognize both as a date and a time. Can someone help build a formula that will separate the necessary data?

Assume my data starts in A1 (there will not be a set number of entries but all entries will be within column A).

Data in A1 looks as follows: Friday, June 24, 2011 at 4:04pm

I need "June 24, 2011" to go into its own column

I need "4:04 PM" to go into its own column --> Also, please note that the time stamp does not put a space between the numeric time and the "AM/PM" so the formula would need to place that space there instead.

Please keep in mind that the dates are for the entire year (so multiple months are represented and times may also contain 4 numeric digits instead of just 3 (ex.12:00).

Thanks for any help you are able to provide.

 

[kpark91]

Hi, is A1 formatted as text?

Anyways here is the way to get ur date.
=LEFT(A1, FIND("at",A1) - 2)

for your time
=RIGHT(A1, LEN(A1) - FIND("at",A1) - 2)

 

[VoG]

Maybe like this

Excel Workbook
	A	B	C
1	Friday, June 24, 2011 at 4:04pm	June-24-2011	4:04 pm
Sheet4

 

[srrboarder]

VoG,

Your formula for the time works brilliantly! Thanks!

I'm having some difficulty with the date formula though. Is there any way to get the result to end up being able to be formatted as a "short date" in excel? I have other formulas referencing these dates (which I've been putting in manually up to this point!).

For example, if you're able to run a "=weeknum" or "weekday" formula off the result in B1, that would be the ideal. Is this possible?

Let me know if I need to explain this better!

Thanks!

 

[barry houdini]

Try this formula to extract the date

=REPLACE(LEFT(A1,FIND("at",A1)-2),1,FIND(",",A1)+1,"")+0

format any way you want......

 

[VoG]

With some extra columns (I think this will depend on your date settings in Windows Control Panel - this is for UK)

Excel Workbook
	A	B	C	D	E
1	Friday, June 24, 2011 at 4:04pm	June-24-2011	24-June2011	24/06/2011	4:04 pm
Sheet4

 

[srrboarder]

Great! Thanks for all your help! I think i should be all set.

Thanks again everyone!

 

[srrboarder]

Barry,
I was able to use your "replace" formula for my date extraction. However, it works for every day except Saturdays. Any idea why? I tried to break down how the formula is referencing the characters within the cell and I'm not seeing why it won't recognize a Saturday's date. Very strange. I've copied an example below.

Saturday, June 25, 2011 at 8:08am

The return i get after using your formula is the "#Value" error.

I appreciate any help you can provide.

Thanks!

 

[barry houdini]

D'oh!

My formula works by finding the "at" between the year and the time......but of course Saturday is the one day of the week which also contains "at".....you can amend formula slightly to search for "at " (with a space), i.e.

=REPLACE(LEFT(A1,FIND("at ",A1)-2),1,FIND(",",A1)+1,"")+0

 

[rjkincaid]

I sure everyone will have a slightly different format, this is typical of the formulas I would use

This should extract the date.
=VALUE(TRIM(LEFT(RIGHT(A1,7),5))&" "&RIGHT(A1,2))

This should extract the day
=VALUE(MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1)-11))