Extract Date From This Text File

Prevost

Board Regular
Joined
Jan 23, 2014
Messages
198
Hi There,

I am trying to extract the date from this text in a cell

10/1/14 12:00:00 AM EDT

The EDT doesn't need to be included. I then want to extract from the date the year, month, day and time (which I can do with formulas).

It's just extracting the date that I need help with. I am okay with using VBA as the solution!

Thanks,
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This will extract the entire Date+Time value.
=SUBSTITUTE(A1," EDT","")+0

If you really want only the Date, then
=INT(SUBSTITUTE(A1," EDT","")+0)

Format the cell with the formula as the date/time of your choice.


Note, depending on your PC's regional settings:
US mm/dd/yyyy - that extracts as October 1st 2014
UK dd/mm/yyyy - that extracts as January 10th 2014
 
Upvote 0
I can get it to extract the text in the "5/14/15 3:00:00 PM" and now need to convert that to a date. Excel doesn't seem to like the DATEVALUE() function when I use the cell with the extracted data. Any ideas? Thanks!
 
Upvote 0
So the date in the text string is written as m/dd/yy
But your pc is using dd/mm/yy
??

Or did you say you got it working now?
 
Upvote 0
I got it working. I had to change the settings in windows so that it recognized the dates differently. You mentioned that in your post. So now it is working and I thank you very much!
 
Upvote 0
You're welcome..

There's still a way that you wouldn't 'need' to actually change your PC settings.
let me know if you'd like to do it that way.
 
Upvote 0
Sure! I would be very interested in knowing. A coworker mentioned the date format of the PC as well and I thought ****, could have figured that out from the post....
 
Upvote 0
OK, now given the original text string you posted..

10/1/14 12:00:00 AM EDT
Is that October 1st, or January 10th ?

If it's Oct 1st, try
=DATE(20&MID(A1,FIND(" ",A1)-2,2),LEFT(A1,FIND("/",A1)-1),SUBSTITUTE(LEFT(REPLACE(A1,1,FIND("/",A1),""),2),"/",""))

If it's Jan 10th, try
=DATE(20&MID(A1,FIND(" ",A1)-2,2),SUBSTITUTE(LEFT(REPLACE(A1,1,FIND("/",A1),""),2),"/",""),LEFT(A1,FIND("/",A1)-1))
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top