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.

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)))