Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count partial dates
Hello,
I need a combination of excel functions that will conduct a (partial) count of the number of dates before a specified ending date. I have a list of dates in a column, let's say these range from year 2000 till 2010. For example, the first few items in this column could read as follows: August 1, 2000, August, 16,2000, September 15, 2000, September 15,2000, September 30,2000 January 23, 2001...December 17,2010 If I need to calculate the # of dates that appear during the month of September, 2000 and prior then I want the count to read 3.033. since two dates started on September 15, which is half of the month of September 2000. Then we had one start on September 30, hence the 0.033 (or 1/30). I'm currently using a countif statement (which would grab the august dates), but not a partial count of the dates starting in september. I tried using a sumproduct statement, but couldn't figure it out either. Any ideas??? I'd rather not use VBA, if there is an excel function(s) to complete this task. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count partial dates
On Tue, 14 Feb 2012 16:47:46 +0000, ringoranger wrote:
Hello, I need a combination of excel functions that will conduct a (partial) count of the number of dates before a specified ending date. I have a list of dates in a column, let's say these range from year 2000 till 2010. For example, the first few items in this column could read as follows: August 1, 2000, August, 16,2000, September 15, 2000, September 15,2000, September 30,2000 January 23, 2001...December 17,2010 If I need to calculate the # of dates that appear during the month of September, 2000 and prior then I want the count to read 3.033. since two dates started on September 15, which is half of the month of September 2000. Then we had one start on September 30, hence the 0.033 (or 1/30). I'm currently using a countif statement (which would grab the august dates), but not a partial count of the dates starting in september. I tried using a sumproduct statement, but couldn't figure it out either. Any ideas??? I'd rather not use VBA, if there is an excel function(s) to complete this task. It is not clear to me, from what you write, what exactly it is you want to do. But to count the number of dates in a list that are in a particular time frame, you can use COUNTIF, COUNTIFS or SUMPRODUCT For example: If your list of dates are in Column A, and you want to count the number of dates in the month of September 2000: =COUNTIF(A:A,"=" & DATE(2000,9,1))-COUNTIF(A:A,""& DATE(2000,9,30)) =COUNTIFS(A:A,"="&DATE(2000,9,1),A:A,"<="& DATE(2000,9,30)) =SUMPRODUCT((A:A=DATE(2000,9,1))*(A:A<=DATE(2000, 9,30))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if with partial text match | Excel Worksheet Functions | |||
Help Calculating Partial Months between 2 dates. | Excel Worksheet Functions | |||
How to filter out all the partial dates from a big list of dates? | Excel Discussion (Misc queries) | |||
sumproduct partial text count | Excel Worksheet Functions | |||
autofilter with range of partial dates | Excel Programming |