Ok, I have two problems that I need to solve with Excel.


I am using Excel 2013 if that helps. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the “7/6/14 8:37PM” out of it. Each text box could potentially be different, so it might not always be in the same format as “Sunday Prime Time 7/6/14 8:37PM” it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time… 8:37PM and not the date, but I would much rather be able to get both the time and date.

THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like “Problem” if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word “Problem” since 5:30PM and 5:48Pm is only 18 minutes apart, and “Problem” after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted. Does this all make sense?
Please let me know if you need more information or if this doesn’t make sense. I really appreciate any help.

You can extract the dates & times with a formaula like:
=--SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND("/",A1)-1-(FIND("/",A1)>2),LEN(A1))),"AM"," AM"),"PM"," PM")
where the string you're processing is in A1. This returns a number which you can format as a date & time if you prefer.

As for the comparisons, are the data sorted by date & time? If so, the comparison requires nothing more than a simple subtraction using the results of the above formula.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]