Comparing dates/time periods

[Melrose0507]

I have been trying to figure this out for many months and can use some desperate help. I have a workbook that will have two worksheets. In my Raw Data, I have a column Q with a text, column X with a time range (ex. 20:00-21:00) and column CG with a date. I want to compare these three items with a list on another tab. The complicated part is I want an exact match for the text and date but want all times that are within the time.

Ex. My raw data has Q. ESPN X. 20:00-21:00 CG. 9/2/10
The tab with my schedule has A. ESPN B. 9/2/10 C. 19:00-22:00

I am looking for a formula that will consider this a match. I have to be carefull as I could have another line in my schedule tab that has A. ESPN B. 9/2/10 C. 12:00-15:00.

I know how to do it if it is an exact match but I need to go a step further. Basically at the end of the day, I want to compare contract lines with a program schedule. If the contract line falls within the program, I want to count it towards the program.

Any direction is greatly appreciated.

 

[pbornemeier]

This formula should give you the number of items on the Raw page that are contained in the range of the items on the Schedule page:

=SUMPRODUCT(--(A2=Raw!$Q$2:$Q$18),--(B2=Raw!$CG$2:$CG$18),--(TEXT(LEFT(C2,5),"hh:mm")<=TEXT(LEFT(Raw!$X$2:$X$18,5),"hh:mm")),--(TEXT(RIGHT(C2,5),"hh:mm")>=TEXT(RIGHT(Raw!$X$2:$X$18,5),"hh:mm")))

1. What sheet will contain the formula that shows a match?
2. Do you want to know if there is one or more items on the Raw page that overlaps an item on the Schedule page?
3. Do you want to know if there is one or more items on the Schedule page that "overlap" an item on the Raw page?
4. What should happen if there are 10 items on the Raw page that match one item on the Schedule page?
5. What should happen if there are 10 items on the Schedule page that match one item on the Raw page?
6. Should 19:00-22:00 match 22:00-23:00? (probably no)
7. Do you use leading 0 for times before 10:00 (08:00)?
8. Do any time ranges cross midnight (00:00)?

 

[Melrose0507]

Thanks for the interest in helping. Below are my responses. Basically I am creating a tracker to tell me all clients falling in each of my listed events on the schedule tab.

1. What sheet will contain the formula that shows a match? The Raw Data sheet will contain the formula. I will be creating a pivot table afterwards.
2. Do you want to know if there is one or more items on the Raw page that overlaps an item on the Schedule page? Yes. I am looking to create a tracker to identify all clients that bought the event. They do not always use the exact event time if they are trying to hit a certain break in the event.
3. Do you want to know if there is one or more items on the Schedule page that "overlap" an item on the Raw page? Yes since they may need to be included as an in event item.
4. What should happen if there are 10 items on the Raw page that match one item on the Schedule page? I want to see all 10
5. What should happen if there are 10 items on the Schedule page that match one item on the Raw page? This should not happen. The items in the Schedule tab should be unique enough to prevent this.
6. Should 19:00-22:00 match 22:00-23:00? (probably no) No.
7. Do you use leading 0 for times before 10:00 (08:00)? Yes
8. Do any time ranges cross midnight (00:00)? No and if it ends at midnight it shows as 24:00. Beginning at midnight shows 00:00.

 

[pbornemeier]

9. Would this be a match?

RAW has Q. ESPN X. 21:00-24:00 CG. 9/2/10
SCHEDULE has A. ESPN B. 9/2/10 C. 19:00-22:00

10. As I understand it, the SCHEDULE page contains a list of events, similar to a TV guide. The DATE/TIME/CHANNEL identifer provides a unique reference to each event.

The RAW worksheet contains entries for customers that have "bought" a particular chunk of DATE/TIME/CHANNEL.

You want make sure that the chunk that they bought actually exists.

11. Can the RAW blocks be more that 1 hour long?
12. Can the RAW blocks be less than 1 hour long?

Could you please provide about 10 more rows of data for the RAW and SCHEDULE worksheets.

 

[Melrose0507]

You are exactly correct in what I am trying to accomplish. Here are the rest of the answers.
9. Would this be a match?

RAW has Q. ESPN X. 21:00-24:00 CG. 9/2/10
SCHEDULE has A. ESPN B. 9/2/10 C. 19:00-22:00
This would not be a match. We want the exact time or shorter.
10. As I understand it, the SCHEDULE page contains a list of events, similar to a TV guide. The DATE/TIME/CHANNEL identifer provides a unique reference to each event.

The RAW worksheet contains entries for customers that have "bought" a particular chunk of DATE/TIME/CHANNEL.

You want make sure that the chunk that they bought actually exists.

This is exactly correct.
11. Can the RAW blocks be more that 1 hour long? Yes
12. Can the RAW blocks be less than 1 hour long? Yes but not less than 30 minutes.

I truly appreciate your help with this. I have worked on this for over a year with little success. This is a problem that has been haunting me.

 

[pbornemeier]

Be sure to read about a problem with formula in the last row of this post.

Put this formula in a cell in Row 2 of the RAW worksheet

=SUMPRODUCT(--(Q2=SCHEDULE!A:A),--(CG2=SCHEDULE!B:B),--(TEXT(LEFT(X2,5),"hh:mm")>=TEXT(LEFT(SCHEDULE!C:C,5),"hh:mm")),--(TEXT(RIGHT(X2,5),"hh:mm")<=TEXT(RIGHT(SCHEDULE!C:C,5),"hh:mm")))

Optionally edit it as follows:
The formula currently examines at each row of columns A, B and C in the Schedule worksheet. You will get better performance if you can give a smaller range of cells (replace A:A with $A$1:$A$3456, B:B with $B$1:$B$3456, etc. -- The range rows must match for each part of the formula) or define a volatile named range for each of those columns. Be sure to include the $ signs (which denote absolute references)

In Excel 2003 has 65536 rows and the performance is OK when looking at the entire column. In Excel 2007 and later has 1048576 rows the the performance when looking at the entire colums is very bad (2-3 seconds per row) each time a row is changed.

Even if you edit the formulas, you may want to turn Auto Recalc off when editing the worksheets then on when you are finished.

Copy it down to the last populated row.

Problem:
In Excel, 24:00 is the same as 00:00. As a result this formula will fail if the end time is 2400. I recommend that you change all occurrences of 24:00 to 23:59 for both worksheets.

Please let me know how this works out.

 

[Melrose0507]

You are an absolute genius. If I understand the results, it will indicate how many matches it finds. In theory there should always only be one match. I can then follow up and delete all rows where the result is zero. Now I have the clients that are falling in the events and just need to create my pivot. You are my excel god! ray:

I can not express how thankful I am to you.

 

[pbornemeier]

Thanks, but there are many better than I. If your workbook has a lot of formulas, it may be best to Copy/Paste Special/Values the formulas in all but the top row until you add new data. That way you can copy down the formula after new data is added, recalculate, and C/PS/V rows 3 and below to speed any pivot table manipulations you do.

Be sure to validate the results before you depend on it for any "real life" functionality.