Number of records between two dates that lasted longer than 1 minute

You could do something like this

The data is the table on the left and is named "Data"

The table on the right summarizes the data. Enter the Start Date, End Date, and Min Duration.

D2=COUNTIFS(Data::B, ">="&C2, Data::A, ">="&A2, Data::A, "<="&B2)

this is shorthand for... select cell D2, then type (or copt and paste from here) the formula:

=COUNTIFS(Data::B, ">="&C2, Data::A, ">="&A2, Data::A, "<="&B2)

Hello

You may try something like this.

REPORT (excerpt) A1 [ stat A2 2016-01-04 A3 2016-01-11 B1 end ] B2 2016-01-10 B3 2016-01-17 C1 count > 1 min C2 =COUNTIFS(LOG::B,">1",LOG::A,">="&A2,LOG::A,"<"&(B2+1)) C3 =COUNTIFS(LOG::B,">1",LOG::A,">="&A3,LOG::A,"<"&(B3+1))

Notes.

Formula in REPORT::C2 can be filled down.

Currently boundaries of date range are inclusive but the length threshold is not.

Date values in A:B are assumed to have time part being 00:00:00.

Table is built with Numbers v2.

Regards,

H

EDIT: fixed boundary condition

HI PD,

Here's an extension to what Wayne has provided. The table set below contains TWO examples, one using the appointment durations in column B of the table "Data", the other using the 'durations' entered as numbers expressing the minutes and parts of minutes in column C of "Data." The entries in each column are the same length of time for the same date.

Example 1:

Columns A and B of Data contain the date and duration in minutes and seconds of each appointment.

Columns A, B and C of Summary contain the start and end dates of the period in which the count is to take place, and the count of appointments in that period lasting more than one minute.

Column A of Minimum contains the minimum length of appointment to be included in the count.

The formula below is entered into Summary::C2, and filled down.

Summary::C2: =COUNTIFS(Data :: $A,">="&$A2,Data :: $A,"<"&$B2,Data :: B,">="&Minimum::A$2)

Example 2:

Columns A and B of Data contain the date and a number corresponding to the duration in minutes and fraction of a minute of each appointment.

Columns A, B and C of Summary contain the start and end dates of the period in which the count is to take place, and the count of appointments in that period lasting more than one minute.

Column B of Minimum contains the minimum length of appointment to be included in the count, expressed as a number indicating that length in minutes.

The formula below is entered into Summary::D2, and filled down.

=COUNTIFS(Data :: $A,">="&$A2,Data :: $A,"<"&$B2,Data :: C,">="&Minimum::B$2)

You wrote: "from a maintenance point of view I think it would be cleaner to use a constant."

Perhaps, providing you will never want to change that constant. If you do change the constant, then you will need ot edit every formula using that constant. If you use a cell reference to pick up the 'constant,' then hanging the constant means making a single change of the value in that one cell.

That said, the formulas are easy to change.

The formula in Summary, column C, which uses the Duration values in Data column B needs the change shown here. Change the formula in C2, then fill to other cells in the column (if needed).

=COUNTIFS(Data :: $A,">="&$A2,Data :: $A,"<"&$B2,Data :: B,">="&DURATION(,,,1))

The formula in Summary, column D, which uses the numeric values in Data column C needs the change shown here. Change the formula in D2, then fill to other cells in the column (if needed).

=COUNTIFS(Data :: $A,">="&$A2,Data :: $A,"<"&$B2,Data :: C,">="&1)

Regards,

Barry