CODEWITHSUNDEEP
exceldatetimeexcel-formulaexcel-2010vlookup
user3285970
user3285970

Reputation: 11

Convert long date text to date / time

I have an Excel (2010) spreadsheet delivered to me which is basically a text dump.

Cell - J8 has the following info Tue Feb 4 00:08:06 EST 2014
Cell - L8 has the following info Tue Feb 4 00:14:54 EST 2014

I need to calculate the difference in time between L8 and J8.

These cells are formatted as "text" and since they have the day, date, time and "EST" formatting the cells with long date it doesn't work.

Upvotes: 1

Views: 289

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If L8 is always later than J8, and if the format is always as you show (with three letters for the timezone, and the spacing as above, then try:

=(MID(L8,5,6)&", "&RIGHT(L8,4) &" "& MID(L8,11,9))-(MID(J8,5,6)&", "&RIGHT(J8,4) &" "& MID(J8,11,9))

One further caveat with this method: your native default date (in Windows Control Panel, not in Excel itself) needs to be MDY as in your text dump.

EDIT: The following (longer) version should work no matter if your native date format is MDY or DMY:

=(MID(L8,9,2)& MID(L8,4,4)&RIGHT(L8,5)&" "&MID(L8,11,9))-(MID(J8,9,2)& MID(J8,4,4)&RIGHT(J8,5)&" "&MID(J8,11,9))

Upvotes: 1

pnuts
pnuts

Reputation: 59450

Uses an array (column of months MMM, and 1-12) and can surely be improved, but to tide you over: 

=DATE(RIGHT(J8,4),VLOOKUP(MID(J8,5,3),array,2,0),MID(J8,9,2))+VALUE(MID(J8,11,9))

in say Y8 and copied across to Z8 with =Z8-Y8 in AA8 may suit (for the 'time' being!)

Does not cater for differences in time zones.

Upvotes: 1

Enigmativity
Enigmativity

Reputation: 117027

I did this to parse the date/time value of each cell:

=DATE(RIGHT(J8,4),VLOOKUP(MID(J8,5,3),$P$2:$Q$13,2,FALSE),MID(J8,9,LEN(J8)-26))
+TIME(MID(J8,11+LEN(J8)-27,2),MID(J8,14+LEN(J8)-27,2),MID(J8,17+LEN(J8)-27,2))

Then you can just do a simple subtraction between the values of the two cells.

The only thing I needed to add to the spreadsheet was a lookup table to map month name to month value. Hence the VLOOKUP in the formula.

I also needed to calculate the offset position past the day value as it appears that it could have either one or two digits - hence the magic 26 and 27 numbers in the formula.

Upvotes: 1

Related Questions