Comparing time values in 2 different sheets

Hi, all.

Regarding the attached spreadsheet, is there a formula that will allow me to flag any time values in sheet 2 that do NOT appear in any of the time ranges in sheet 1?

Kind thanks for any help.

NOTE: I cross-posted in one other location.

http://forum.chandoo.org/threads/fla...-sheets.30730/

Please let us know if you have found your solution (in both posts) and include where that solution is.

In the attached I made three helper columns to combine the dates with the times. This simplifies the final formula and resolves any possible trans-midnight calculations should they occur.

In Sheet1 I just added the dates to "In" and did the same with "Out" (columns E:F). In Sheet2 I added the dates to the target times.

Then in column D of Sheet2 this formula

Formula:

Please Login or Register  to view this content.

You are welcome. Glad to hear it. Thank you for the feedback.

Yes, and if I understand you correctly you will need to edit the formula ranges 'Sheet 1'!$E$2:$E$2081 and 'Sheet 1'!$F$2:$F$2081 to be at least the size of the new source data range.

A good strategy for future data additions and minimal future formula edits is to write the formula with ranges a little bit larger than anticipated needs. If for example you anticipate a maximum of 3000 rows of data consider making the formula range 'Sheet 1'!$E$2:$E$3500 or even 'Sheet 1'!$E$2:$E$4000.

The main point being:
You want to avoid yielding to the temptation to reference whole columns (that's 1,048,576 rows). Doing that forces Excel to do over one million unnecessary calculations multiple times. It slows the workbook down.

You're welcome.