Extracting 2 dates within a paragraph in a cell

[franzieperez]

Hi there,

I am trying to get an average time for work being done by searching through work logs and taking the starting and ending dates and subtracting them from each other.

The problem is that each work log is its own cell, so I have been extracting the dates manually up until now.

Luckily, both of the dates are contained in one sentence, which is standardized as: "The total time spent on this ticket from 6/28/2011 9:44:13 AM to 7/8/2011 10:51:30 AM is mins." This sentence is not at the very beginning or end of the paragraph, and it is not even in a standardized position.

Is there a way to extract the dates using a function and place them in the 2 cells to the right of the source cell?

Thanks.

[NBVC]

These worked for the sample you supplied, assuming in A1...

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

both need to be formatted as Date/Time.

[Richard Buttrey]

Hi,

One way - (probably of several)

B1:

Please Login or Register  to view this content.

C1:

Please Login or Register  to view this content.

[ben_hensel]

Yes. It can be done:

To pull "6/28/2011 9:44:13 AM to 7/8/2011 10:51:30 AM" from inside a text string in cell A1:

Please Login or Register  to view this content.

Then:
B1 = first value
C2 = second value

Please Login or Register  to view this content.

Note you will have to format your cell to a custom format ("mm/dd/yyyy h:mm" or something like that) to display both date and time.

[franzieperez]

@ben_hensel

I do get a few errors for the 2nd value, and I can't seem to figure out why.

Regardless, this has helped me to populate about 300+ cells, so I'll call it a "SOLVED" for now.

Thanks!

[Haseeb Avarakkan]

Or may be,

First Date,

Please Login or Register  to view this content.

Second Date

Please Login or Register  to view this content.