Flagging differences between time values in 2 different sheets

[Preston]

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've cross-posted in one other location.

http://www.excelforum.com/excel-for...paring-time-values-in-2-different-sheets.html

 

[p45cal]

in cell C2 of Sheet2:

=SUMPRODUCT((('Sheet 1'!$A$2:$A$2081 + 'Sheet 1'!$B$2:$B$2081)<=(A2+B2))*(('Sheet 1'!$A$2:$A$2081 + 'Sheet 1'!$C$2:$C$2081)>=(A2+B2)))=0

and copy down.
TRUE if the date isn't in any range.
In the attached is that formula in column C, but also added formulae in columns D to G to show which range the date/time is in.
The formula in column C is reliable if there is more than one row satisfying the criteria, but columns D to G won't be. Luckily, this isn't the case with your data.

 

[Preston]

Thank you.

Question resolved.