| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help - Function Count, but on dates not numbers
Hi, not sure if I can get help here on this are not but.....
Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
Try one of these:
When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
On Wed, 17 Oct 2007 13:13:00 -0700, Nadine
wrote: Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine =countif(rng,"="&StartDt) - countif(rng,""&EndDt) --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
Hi,
Wondering if you might be able to help me with a similar question? I've got a column (D) which has dates that a request was approved. I want to calculate how many requests were approved per month over a two year period. Is there a away to do this other than using "less than" and "greater than" for each month? Thanks! "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
On Thu, 18 Oct 2007 07:46:04 -0700, KellyF
wrote: Hi, Wondering if you might be able to help me with a similar question? I've got a column (D) which has dates that a request was approved. I want to calculate how many requests were approved per month over a two year period. Is there a away to do this other than using "less than" and "greater than" for each month? Thanks! You can probably do that with a Pivot Table --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
tried the formula:
=COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
Try
=COUNTIF(A1:A20,"="&DATE(2007,4,1))-COUNTIF(A1:A20,""&DATE(2008,3,31)) -- Regards, Peo Sjoblom "Nadine" wrote in message ... tried the formula: =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
Further to post below - looks like the results is doing a subtraction an
given the balance. But not wanting a subtraction, but a count of how many records are within that date frame. Thanks "Nadine" wrote: tried the formula: =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
That particular formula will not count the start and end dates.
This "problem" arises quite often when someone makes a post and says: Count *between* x and y when they meant to say count *from* x to y. For example, if I say count the numbers between 1 and 10 that literally means count 2,3,4,5,6,7,8,9. So, to count the numbers *from* 1 to 10: =COUNTIF(A1:A20,"=1")-COUNTIF(A1:A20,"10") To count the numbers *between* 1 AND 10: =COUNTIF(A1:A20,"1")-COUNTIF(A1:A20,"=10") -- Biff Microsoft Excel MVP "Nadine" wrote in message ... tried the formula: =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
THANKS!!!!! got it now, its working. happy happy. thanks.
"T. Valko" wrote: That particular formula will not count the start and end dates. This "problem" arises quite often when someone makes a post and says: Count *between* x and y when they meant to say count *from* x to y. For example, if I say count the numbers between 1 and 10 that literally means count 2,3,4,5,6,7,8,9. So, to count the numbers *from* 1 to 10: =COUNTIF(A1:A20,"=1")-COUNTIF(A1:A20,"10") To count the numbers *between* 1 AND 10: =COUNTIF(A1:A20,"1")-COUNTIF(A1:A20,"=10") -- Biff Microsoft Excel MVP "Nadine" wrote in message ... tried the formula: =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help - Function Count, but on dates not numbers
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Nadine" wrote in message ... THANKS!!!!! got it now, its working. happy happy. thanks. "T. Valko" wrote: That particular formula will not count the start and end dates. This "problem" arises quite often when someone makes a post and says: Count *between* x and y when they meant to say count *from* x to y. For example, if I say count the numbers between 1 and 10 that literally means count 2,3,4,5,6,7,8,9. So, to count the numbers *from* 1 to 10: =COUNTIF(A1:A20,"=1")-COUNTIF(A1:A20,"10") To count the numbers *between* 1 AND 10: =COUNTIF(A1:A20,"1")-COUNTIF(A1:A20,"=10") -- Biff Microsoft Excel MVP "Nadine" wrote in message ... tried the formula: =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) But the results provide an incorrect count. We have a column with multiple dates over 3 years. But want to only count how many records there are that are between the dates 01-APR-07 and 31-MAR-08. If count manually should get 41 records, but calculation gives results of 37. Thanks "T. Valko" wrote: Try one of these: When you say: greater then "specific date" and less then "specific date". I'm assuming you do not want to include the start date and the end date. =COUNTIF(A1:A20,""&DATE(2007,7,1))-COUNTIF(A1:A20,"="&DATE(2007,7,31)) Or: C1 = 7/1/2007 D1 = 7/31/2007 =COUNTIF(A1:A20,""&C1)-COUNTIF(A1:A20,"="&D1) If you *do* want to include the start date and end date: =COUNTIF(A1:A20,"="&DATE(2007,7,1))-COUNTIF(A1:A20,""&DATE(2007,7,31)) =COUNTIF(A1:A20,"="&C1)-COUNTIF(A1:A20,""&D1) -- Biff Microsoft Excel MVP "Nadine" wrote in message ... Hi, not sure if I can get help here on this are not but..... Trying to create a formula where it counts the number of records that is greater then .... but less then.... Problem is I know how to do this if the cells contain numbers. But can't figure out how to do the formula where the cells/columns have DATEs as it's contents. Want the formula to - count the number of records that have dates that are greater then "specific date" and less then "specific date". And return the results of how many records meet that criteria. Thanks if any one can help. Nadine |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function to count numbers in one Cell IE 1+2+10 | Excel Worksheet Functions | |||
| count if function using dates | Excel Discussion (Misc queries) | |||
| COUNT dates in ColumnY based on numbers in ColumnX | Excel Worksheet Functions | |||
| Using which function to calculatiing 2 set of numbers of days from 3 dates | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Linear Mode
