You are here:

Excel/Corrected Question - Exclude other timeframes from one timeframe

Question
Hi Edrawd,
When I sent you the question by that time I was also trying to find out the solution but I found that The Expected output i sent in previous question itself was wrong.

I am very sorry for that. Please remove the question from question pool.

Here is my revised question. (Just removed one row from previous questions' output else is same.)

I have one time frame-1.
03/10/2015 01:40:00 to 04/10/2015 02:30:00

And a list of other time frames.
03/10/2015 02:07:00 to 03/10/2015 08:27:00
11/10/2015 08:27:00 to 12/10/2015 07:27:00
03/10/2015 04:07:00 to 03/10/2015 05:27:00
13/02/2014 07:27:00 to 14/02/2014 07:12:00
04/10/2015 01:12:00 to 05/10/2015 07:44:00
25/10/2013 07:44:00 to 26/10/2013 09:01:00

I want to find all the fraction of the timeframe-1 as output; which are "untouched" of other times listed.
If there is no time in the list which touches the timeframe-1; then the whole timeframe-1 is the output.

In above example, here is the output.
03/10/2015 01:40:00 to 03/10/2015 02:07:00
03/10/2015 08:27:00 to 04/10/2015 01:12:00

If there was no time in the list which touches the timeframe-1; then the whole timeframe-1 was the output.

Can you help?
Thanks.

Ok. Now what I have tried is something like this.
A1=03/10/2015 01:40
B1=04/10/2015 02:30

C1:C6=
03/10/2015 02:07
11/10/2015 08:27
03/10/2015 04:07
13/02/2014 07:27
04/10/2015 01:12
25/10/2013 07:44

D1:D6=
03/10/2015 08:27
12/10/2015 07:27
03/10/2015 05:27
14/02/2014 07:12
05/10/2015 07:44
26/10/2013 09:01

Formula in F1 =SMALL(\$A\$1:\$D\$6,ROW(AN1))
Dragged Down. So all the fraction are listed in sorted order.

Results are these:
25/10/2013 07:44
26/10/2013 09:01
13/02/2014 07:27
14/02/2014 07:12
03/10/2015 01:40 <----
03/10/2015 02:07
03/10/2015 04:07
03/10/2015 05:27
03/10/2015 08:27 <----
04/10/2015 01:12
04/10/2015 02:30
05/10/2015 07:44
11/10/2015 08:27
12/10/2015 07:27

Now We have to bring out the Untouched Timeframe.

For that I think we need to use Sumproduct formula (I am trying with it but don't know exactly how to implement.)
But we have to check each time that whether it is within any of the timeframes listed. (If it is within that, then we don't need that.)
Then again we can sort untouched times and consider the second smallest as ending point of output timeframe; the first smallest is the Starting point of the frame.

Can you help?
Thanks

I can see the question, but it is not totally clear to me what you are trying to return - are you looking for the times which are before or after the earliest and latest values in A1 & B1?  It may help if you could send a sample sheet to me - my direct email is support@flameenterprises.co.uk
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment You have sent exactly the same formula that I wanted. Thanks so much for your time, efforts and humble attitude.

• View Follow-Ups
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!