CODEWITHSUNDEEP
exceldateextract
G198
G198

Reputation: 43

Identifying date in text file While date is incorrectly formatted

I need to extract the date from C2 and find the difference between the date in c2 and A1

The date is formatted as "Jul, 18 2015", any ideas? EDIT the database has a different amount of text per cell. Is there away around this so that i can apply the formula to every cell and pull the day/month/year?

EDIT so the functions listed in the answer don't work on varied text

Upvotes: 1

Views: 58

Answers (1)

Forward Ed
Forward Ed

Reputation: 9884

you are going to need to go through a series of string manipulation and date time functions. Lets start by assuming your string is in the C2 cell. In order to do this we are going to work from the largest unit (years) to the smallest unit (days). You can do it in any order as it will all be lumped into once formula, but for the breakdown of steps its good to have an order.

Step 1) PULL OUT THE YEAR

=MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,4)

That will give us the last 4 characters of the string after the second space which in this case is the year.

Step 2) PULL OUT THE MONTH

=MONTH(DATEVALUE(MID(C2,2,3)&"-"&1))

that looks at second character and pulls out the string 3 character long which is your month. It then converts it to a format that excel tends to recognize as a date short form by adding a - and the digit 1 to it. So in your case if would look like Jul-1. Datevalue converts this to an excel date serial, which we then pull back and grab the month from and in your case that is 7

If the above formula does not work for you it could be due to regional settings. If that is the case you can use the following:

=MATCH(MID(C2,2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)

If you use this alternat formula , be sure to adjust the final equation accordingly.

Step 3) PULL OUT THE DAY

=TRIM(MID(C2,FIND(" ",C2)+1,2))

So the above formula finds the first space and then starts pulling then next 2 characters after it. Now since I do not know if the first of the month is 01 or just 1, it may wind up grabbing the space after the 1. The trim function removes excess spaces.

Step 4) BUILD THE DATE

The DATE function in excel requires the YEAR, MONTH, and DAY and converts those values in to the excel date serial. In this case we will convert:

=DATE(year,month,day)

to the following by substituting our equations from above:

=DATE(MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,4),MONTH(DATEVALUE(MID(C2,2,3)&"-"&1)),TRIM(MID(C2,FIND(" ",C2)+1,2)))

The final touch is to ensure your cell is formatted as date and not General or some other format which will result as the date being displayed in an integer format.

Now assuming you date in A1 is in Excel format, you would simply add A1- to the front of the last formula to give you:

=A1-DATE(MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,4),MONTH(DATEVALUE(MID(C2,2,3)&"-"&1)),TRIM(MID(C2,FIND(" ",C2)+1,2)))

Now, if A1 is also in quotes like the C2 formula, repeat the formula for stripping the date out of C2 but use A1 as the reference and substitute it in for A1 in the last formula to give:

=DATE(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,4),MONTH(DATEVALUE(MID(A1,2,3)&"-"&1)),TRIM(MID(A1,FIND(" ",A1)+1,2)))-DATE(MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,4),MONTH(DATEVALUE(MID(C2,2,3)&"-"&1)),TRIM(MID(C2,FIND(" ",C2)+1,2)))

FORMULAS USED

Text/String Functions

Date/Time Functions

Upvotes: 1

Related Questions