| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
=sumproduct((a2:a22=b1)*(a2:a22<b2))
-- Don Guillett SalesAid Software "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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Formula
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula structure building ? check under the excel forum.... | Excel Discussion (Misc queries) | |||
| Excel Formula Issue | Excel Discussion (Misc queries) | |||
| Match then lookup | Excel Worksheet Functions | |||
| converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
| How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Linear Mode
