Occurences between 2 dates
 

I have a start and end date on one sheet, another sheet with a list of dates
in the month.
I need to know how many instances the item was active on a given date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list of
dates
in the month.
I need to know how many instances the item was active on a given date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

Thanks Bob
Now I need to find the euiqvalent in Access

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list of
dates
in the month.
I need to know how many instances the item was active on a given date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

Is it possible to add another dimension to the instances per date, can a
filter of another column with citeria of say "Airbus"
Start End Fleet
1/1/2009 1/2/2009 767
1/1/2009 1/3/2009 Airbus
1/1/2009 1/6/2009 767
1/1/2009 1/5/2009 Airbus
1/1/2009 1/3/2009 Airbus

So I'd like to filter on "Airbus" using the
SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))


"Bernie" wrote:

Thanks Bob
Now I need to find the euiqvalent in Access

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list of
dates
in the month.
I need to know how many instances the item was active on a given date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1$C$1:$C$10="Airbus"))


--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
Is it possible to add another dimension to the instances per date, can a
filter of another column with citeria of say "Airbus"
Start End Fleet
1/1/2009 1/2/2009 767
1/1/2009 1/3/2009 Airbus
1/1/2009 1/6/2009 767
1/1/2009 1/5/2009 Airbus
1/1/2009 1/3/2009 Airbus

So I'd like to filter on "Airbus" using the
SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))


"Bernie" wrote:

Thanks Bob
Now I need to find the euiqvalent in Access

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list of
dates
in the month.
I need to know how many instances the item was active on a given
date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

Thanks Bob
That was dahhhhhh on my part, referencec the wrong sheet
Thanks again

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1$C$1:$C$10="Airbus"))


--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
Is it possible to add another dimension to the instances per date, can a
filter of another column with citeria of say "Airbus"
Start End Fleet
1/1/2009 1/2/2009 767
1/1/2009 1/3/2009 Airbus
1/1/2009 1/6/2009 767
1/1/2009 1/5/2009 Airbus
1/1/2009 1/3/2009 Airbus

So I'd like to filter on "Airbus" using the
SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))


"Bernie" wrote:

Thanks Bob
Now I need to find the euiqvalent in Access

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list of
dates
in the month.
I need to know how many instances the item was active on a given
date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie

Occurences between 2 dates
 

<G I must admit it did leave me wondering.

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
Thanks Bob
That was dahhhhhh on my part, referencec the wrong sheet
Thanks again

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1),--(Sheet1$C$1:$C$10="Airbus"))


--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
Is it possible to add another dimension to the instances per date,
can a
filter of another column with citeria of say "Airbus"
Start End Fleet
1/1/2009 1/2/2009 767
1/1/2009 1/3/2009 Airbus
1/1/2009 1/6/2009 767
1/1/2009 1/5/2009 Airbus
1/1/2009 1/3/2009 Airbus

So I'd like to filter on "Airbus" using the
SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))


"Bernie" wrote:

Thanks Bob
Now I need to find the euiqvalent in Access

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$10<=A1),--(Sheet1!$B$1:$B$10=A1))

--
__________________________________
HTH

Bob

"Bernie" wrote in message
...
I have a start and end date on one sheet, another sheet with a list
of
dates
in the month.
I need to know how many instances the item was active on a given
date
such as
1/1/09 - 5
1/3/09 - 4
1/6/09 - 1
Start End
1/1/2009 1/2/2009
1/1/2009 1/3/2009
1/1/2009 1/6/2009
1/1/2009 1/5/2009
1/1/2009 1/3/2009

Thanks Bernie