ExcelBanter - Excel Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel Formula (https://www.excelbanter.com/excel-discussion-misc-queries/129083-excel-formula.html)

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

It's just a subtraction problem. "=b1-a1" for example. Then you will want
to format the cell where the formula is to show a number and the result will
be the number of days between the two dates.

Steve

"Steve" wrote in message
...
Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

Use Datedif:

=DATEDIF(A1,A2,"md")+1

--
Gary's Student
gsnu200703

"Steve" wrote:

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

Assuming your dates are in column A

=COUNTIF(A:A,"<=1/31/07")-COUNTIF(A:A,"<1/2/07")

Vaya con Dios,
Chuck, CABGx3

"Steve" wrote in message
...
Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

If you're starting with a list of dates and you want to know how many are
within the range 01/02/2007 through 01/31/2007 (inclusive),
you'll probably get SUMPRODUCT responses,
but lately I've developed a new respect for the FREQUENCY function as an
alternative.

With
A1:A100 containing dates

Try this...
B1: (the end date of the prev range....eg 01/01/2007)
C1: (the end date of the range you want....eg 01/31/2007)

This formula returns the number of cells in A1:A100 that are within the
range 01/02/2007 and 01/31/2007, inclusive.

E1: =INDEX(FREQUENCY(A1:A100,C1:D1),2)

It works this way....

A1:A100 is the list of dates

C1:D1 contains these dates
01/01/2007
01/31/2007

The FREQUENCY function returns an array of 3 values (in this case):
The number of cells that are <=01/01/2007
The number of cells that are 01/01/2007 and <=01/01/2007
The number of cells that are 01/31/2007
(you don't have to specify the last range...Excel assumes it's there and
uses it)

The INDEX function looks at array of 3 values and pulls the 2nd value

Example:
If A1:A100 only contains the list of dates from
12/15/2006 through 02/15/2007,
the FREQUENCY function returns this array: {18,30,15}
which is....
the 18 days prior to 01/02/2007,
the 30 days within 01/02/2007 thru 01/31/2007,
and the 15 days after 01/31/2007

In the example....the formula returns 30

You could also write the formula this way:
=INDEX(FREQUENCY(A1:A100,--{"01-Jan-2007","31-Jan-2007"}),2)

Does that help?

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP

"Steve" wrote:

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

But be aware that the "md" will give results which may be confusing when you
go beyond a month.
--
David Biddulph

"Gary''s Student" wrote in message
...
Use Datedif:

=DATEDIF(A1,A2,"md")+1

"Steve" wrote:

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

Yet another clever idea!

Just a thought......

It may be more intuitive to use the intended date range and adjust in the
formula:

B1 = 01/02/2007
C1 = 01/31/2007

=INDEX(FREQUENCY(A1:A100,B1:C1-{1,0}),2)

We know that Countif(.....)-Countif(.....) is more efficient than the
Sumproduct version. I'm wondering if this Frequency version might be more
efficient than the Countif version?

Biff

"Ron Coderre" wrote in message
...
If you're starting with a list of dates and you want to know how many are
within the range 01/02/2007 through 01/31/2007 (inclusive),
you'll probably get SUMPRODUCT responses,
but lately I've developed a new respect for the FREQUENCY function as an
alternative.

With
A1:A100 containing dates

Try this...
B1: (the end date of the prev range....eg 01/01/2007)
C1: (the end date of the range you want....eg 01/31/2007)

This formula returns the number of cells in A1:A100 that are within the
range 01/02/2007 and 01/31/2007, inclusive.

E1: =INDEX(FREQUENCY(A1:A100,C1:D1),2)

It works this way....

A1:A100 is the list of dates

C1:D1 contains these dates
01/01/2007
01/31/2007

The FREQUENCY function returns an array of 3 values (in this case):
The number of cells that are <=01/01/2007
The number of cells that are 01/01/2007 and <=01/01/2007
The number of cells that are 01/31/2007
(you don't have to specify the last range...Excel assumes it's there and
uses it)

The INDEX function looks at array of 3 values and pulls the 2nd value

Example:
If A1:A100 only contains the list of dates from
12/15/2006 through 02/15/2007,
the FREQUENCY function returns this array: {18,30,15}
which is....
the 18 days prior to 01/02/2007,
the 30 days within 01/02/2007 thru 01/31/2007,
and the 15 days after 01/31/2007

In the example....the formula returns 30

You could also write the formula this way:
=INDEX(FREQUENCY(A1:A100,--{"01-Jan-2007","31-Jan-2007"}),2)

Does that help?

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP

"Steve" wrote:

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve

Not sure about the "more intuititive" part...
Since FREQUENCY is already on the fringes of arcane, I thought it would be
easier to explain how the "bins" work without tweaking the inputs with an
array constant and having to explain how THAT works, too!

***********
Regards,
Ron

XL2002, WinXP

"T. Valko" wrote:

Yet another clever idea!

Just a thought......

It may be more intuitive to use the intended date range and adjust in the
formula:

B1 = 01/02/2007
C1 = 01/31/2007

=INDEX(FREQUENCY(A1:A100,B1:C1-{1,0}),2)

We know that Countif(.....)-Countif(.....) is more efficient than the
Sumproduct version. I'm wondering if this Frequency version might be more
efficient than the Countif version?

Biff

"Ron Coderre" wrote in message
...
If you're starting with a list of dates and you want to know how many are
within the range 01/02/2007 through 01/31/2007 (inclusive),
you'll probably get SUMPRODUCT responses,
but lately I've developed a new respect for the FREQUENCY function as an
alternative.

With
A1:A100 containing dates

Try this...
B1: (the end date of the prev range....eg 01/01/2007)
C1: (the end date of the range you want....eg 01/31/2007)

This formula returns the number of cells in A1:A100 that are within the
range 01/02/2007 and 01/31/2007, inclusive.

E1: =INDEX(FREQUENCY(A1:A100,C1:D1),2)

It works this way....

A1:A100 is the list of dates

C1:D1 contains these dates
01/01/2007
01/31/2007

The FREQUENCY function returns an array of 3 values (in this case):
The number of cells that are <=01/01/2007
The number of cells that are 01/01/2007 and <=01/01/2007
The number of cells that are 01/31/2007
(you don't have to specify the last range...Excel assumes it's there and
uses it)

The INDEX function looks at array of 3 values and pulls the 2nd value

Example:
If A1:A100 only contains the list of dates from
12/15/2006 through 02/15/2007,
the FREQUENCY function returns this array: {18,30,15}
which is....
the 18 days prior to 01/02/2007,
the 30 days within 01/02/2007 thru 01/31/2007,
and the 15 days after 01/31/2007

In the example....the formula returns 30

You could also write the formula this way:
=INDEX(FREQUENCY(A1:A100,--{"01-Jan-2007","31-Jan-2007"}),2)

Does that help?

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP

"Steve" wrote:

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Any help would be appriciated

Steve