I have a text in a single cell in Excel (exported from a database) which contains random information with timestamps (date and time). I want to extract these time stamps to calculate the time between them. I found ways to extract one of these dates from a cell, but how can I extract all these dates/times to individual cells? example of text in one cell:

*****************************************************************

13/08/2013 18:14:12 Case 123456. values Changed to: BAU 13/08/2013 18:15:04 We have accepted the service today 13/08/2013 18:59:50 I will review to update records 13/08/2013 19:15:21 The service is ready 13/08/2013 19:17:51 Incident Ended at 13/08/13 17:14:00 GMT 14/08/2013 14:42:04 Closure at 14/08/2013 13:40:00 GMT . Status changed to CLOSED. 14/08/2013 14:42:08 CLOSURE CONFIRMED

*****************************************************************

message edited by kimpep

✔ Best Answer

I made a couple of changes, that might speed things up,

I reduced it from Three Steps to Two Steps by combining

the last two steps into one.With your data in cell A1

First:

In cell B1 enter the formula: =SEARCH("??/??/2013",$A$1,1)

In cell C1 enter the formula: =SEARCH("??/??/2013",$A$1,B$1+20)

Drag cell C1 across to Column I, that is were your dates end.As you can see, the SEARCH() function allows the use of Wild Card characters,

so we can find the actual beginning without doing any math.Second:

Now that we have the starting locations of each of the dates

we can combine formulas and save ourselves a step:In cell B2 enter the formula: (It's a bit long, so copy and paste.)

=DATE(MID(MID($A$1,B1,19),7,4),MID(MID($A$1,B1,19),4,2),LEFT(MID($A$1,B1,19),2))+TIMEVALUE(RIGHT(MID($A$1,B1,19),8))

Drag cell B2 across to Column I,

this cuts out the date & times from A1 and at the same time

coverts it from TEXT to real Dates & Times.Makes life a bit easier.

MIKE

This is not the most elegant of ways, but it gets the job done. You will need to do Three separate steps.

With your data in cell A1

First:

In cell B1 enter the formula: =FIND("2013",$A$1,1)

In cell C1 enter the formula: =FIND("2013",$A$1,B$1+1)

Drag cell C1 across to Column I, that is were your dates end.These formulas get the starting location of each of the Date year in cell A1

Second:

Now that we have the starting locations of each of the dates:

In cell B2 enter the formula: =MID($A$1,B$1-6,19)

Drag cell B2 across the Column I,

this cuts out the date & times from A1

you should now have all your Dates and Times in their own cells,

but your not finished, as the data is still in TEXT format.Third:

To get Real Dates and Times

In cell B3 enter the formula:

~~=DATE(RIGHT(B2,4),MID(B2,FIND("/",B2,1)+1,2),LEFT(B2,2))+TIMEVALUE(RIGHT(B2,8))~~=DATE(MID(B2,7,4),MID(B2,4,2),LEFT(B2,2))+TIMEVALUE(RIGHT(B2,8))

You should now have Real dates and times and you can now do your computations.

Quick and Dirty, but it works.

Edit: Changed last formula.

MIKE

message edited by mmcconaghy

Many thanks for the reply and useful information Mike,

I tested your suggestion and it works :-)

I have a lot of data, and the number of date× in a single cell can vary, but the formulas which you provided will help me to continue and eventually get what I need.

kimpep, There is an alternative way to get the data you need, it's a bit more involved then simply using three formulas:

You could use the

Text to Columnswizard,

using the Space character as your delimiter.

Then you would have each data bit in it's own cell

you can also specify those cell that contain Dates, as Date cells,

you could then simply delete any columns that are not dates and times.You would probably need to dedicate one sheet for this process, as the column count could get fairly large.

Once you get the Dates & Times you may still need to convert them from Text to real Dates and Times depending on how well the Wizard works.

Also, as the data is being exported from a database, if you could get the data configured as a CSV (Comma Separated Values) it would make your life a bit easier, as Excel would then automatically import the data into separate cells, then you would need only delete those cells you don't need.

Or, prepossesses the data yourself, if you can, so it is a CSV.MIKE

Thanks again Mike, I used the Text-to-column feature, but I didn't suite my needs as much as your suggested formulas.

As for the export from a database to a .csv file: this is already the case, this cell info, which I showed, is just one one many. I will have to calculate the time between the timestamps and compare with some criteria from other cell info, to see if they meet the SLA's.

I will then convert a weekly summary into graphs.

I made a couple of changes, that might speed things up,

I reduced it from Three Steps to Two Steps by combining

the last two steps into one.With your data in cell A1

First:

In cell B1 enter the formula: =SEARCH("??/??/2013",$A$1,1)

In cell C1 enter the formula: =SEARCH("??/??/2013",$A$1,B$1+20)

Drag cell C1 across to Column I, that is were your dates end.As you can see, the SEARCH() function allows the use of Wild Card characters,

so we can find the actual beginning without doing any math.Second:

Now that we have the starting locations of each of the dates

we can combine formulas and save ourselves a step:In cell B2 enter the formula: (It's a bit long, so copy and paste.)

=DATE(MID(MID($A$1,B1,19),7,4),MID(MID($A$1,B1,19),4,2),LEFT(MID($A$1,B1,19),2))+TIMEVALUE(RIGHT(MID($A$1,B1,19),8))

Drag cell B2 across to Column I,

this cuts out the date & times from A1 and at the same time

coverts it from TEXT to real Dates & Times.Makes life a bit easier.

MIKE

Thanks again Mike, combining formulas makes the results a bit more obvious to read.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History