If date between any of multiple date ranges

Hi,

I would be sooooo grateful for anyone's help. I am trying to solve a problem with patient flow through operating theatres. What I need to be able to do is:

StartDateAndTime1 EndDateAndTime1
StartDateAndTime2 EndDateAndTime2
...
StartDateAndTimeN EndDateAndTimeN

If ThisDateAndTime is between any of the pairs of StartDateAndTime and EndDateAndTime then value of cell is 1

Can anyone help? Thank you very much.

Mark.

attach excel file with some dummy data and result what you required

Use IF Function

Hi,

Thank you for taking an interest. Is this adequate?

BW

Mark.

Hi pakhare.kiran

I would but there are _thousands_ of start and finish times...

Mark

Use this formula in E1 Cell in your patientflowexample workbook

=SUMPRODUCT(($D1>=TEXT($A$2:$A$7,"HH:MM")*1)*($D1<=TEXT($B$2:$B$7,"HH:MM")*1))

Originally Posted by nflsales

Use this formula in E1 Cell in your patientflowexample workbook

=SUMPRODUCT(($D1>=TEXT($A$2:$A$7,"HH:MM")*1)*($D1<=TEXT($B$2:$B$7,"HH:MM")*1))

You are a flippin' genius! Thank you thank you thank you! I've not got the first clue how that works at the moment but I will spend a good while making sure I do understand it eventually. Thanks a million, you've made my life much easier.

Best wishes,

Mark.

Originally Posted by nflsales

Use this formula in E1 Cell in your patientflowexample workbook

=SUMPRODUCT(($D1>=TEXT($A$2:$A$7,"HH:MM")*1)*($D1<=TEXT($B$2:$B$7,"HH:MM")*1))

Hi nflsales. I have a follow-up question to this fantastic solution. How would you adjust the formula if the values in Col D were also in a date & time format (e.g., 4/3/2017 10:15:11 AM) instead of just in a HH:MM format?
Thank you!