Count if between date parameters

[miroshak]

I need create a formula that show how many patients were available to be seen
on each day of the month. I would like to make the cut-off for changes of
dates as 3pm, meaning that one day is from 3pm - 3pm the next day.

e.g. - 12/30/09 available patients would be all those there from 3:01pm on
12/29/09 to 3:00pm on 12/30/09.

If they're available for the full day count them only. Using this logic with
the data below on 12/30/09 only 2 patients were available to be seen.

Name Request DT Res DT
P1 12/27/2009 1:26 12/31/2009 16:28
P2 12/27/2009 12:00 12/30/2009 7:20
P3 12/23/2009 9:09 12/23/2009 9:12
P4 12/18/2009 10:25 12/18/2009 12:44
P5 12/29/2009 6:04 12/31/2009 9:20
P6 12/13/2009 15:37 12/13/2009 15:39

 

[Stefi]

Your question is not clear enough for an outsider, because you don't specify
clearly the criterium of being available, what do "Request DT" and "Res DT"
mean, but maybe this formula gives you a guide: if days are in column D then
enter formula in E2 and fill it down as required; it counts values in C2:C7
between 3pm - 3pm the next day.

Clarify yor request for more help!


--
Regards!
Stefi



„miroshak†ezt írta:

 

[Jacob Skaria]

With the query date 12/30/09 in cell D1 try the below formula...

=SUMPRODUCT((B1:B10<D1-1+TIME(15,0,0))*(C1:C10>=D1+TIME(15,0,0)))

 

[Bob Phillips]

Assuming the date to check is in cell E1, try

=SUMPRODUCT(--(B2:B7<=$E$1-1+"15:00:00"),--(C2:C7>=$E$1+"15:00:00"))

HTH

Bob

 

[Stefi]

Sorry, forgot to paste the formula:

=SUMPRODUCT(--($C$2:$C$7>=D2-1+TIME(15,0,0)),--($C$2:$C$7<=D2+TIME(15,0,0)))


--
Regards!
Stefi



„Stefi†ezt írta: