Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count if with partial text match [email protected] Excel Worksheet Functions 0 June 2nd 09 03:49 PM
Help Calculating Partial Months between 2 dates. sirscottyog Excel Worksheet Functions 2 April 16th 09 03:29 AM
How to filter out all the partial dates from a big list of dates? Tiwarr Excel Discussion (Misc queries) 1 September 14th 06 05:35 PM
sumproduct partial text count Ribeye Excel Worksheet Functions 2 February 14th 06 07:43 PM
autofilter with range of partial dates François Excel Programming 3 February 28th 05 03:01 PM


All times are GMT +1. The time now is 05:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"