PC Review


Reply
Thread Tools Rate Thread

counting based on two dates

 
 
AccessNoviceButTrying
Guest
Posts: n/a
 
      20th Nov 2009
I have two columns which both contains dates. I want a count based on
criteria from the dates. For example...

Start Date End Date
01/01/08 12/31/08
3/15/07 12/01/09
5/5/09 10/01/09
10/01/08

How would i get a total count of items that started before 06/01/08 and
ended after 06/01/09.

it would be 1 in the example above.

Thanks
 
Reply With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      20th Nov 2009
Try this

=SUMPRODUCT((A2:A20>DATE(2008,6,1))*(B2:B20<DATE(2009,6,1)))

Mike

"AccessNoviceButTrying" wrote:

> I have two columns which both contains dates. I want a count based on
> criteria from the dates. For example...
>
> Start Date End Date
> 01/01/08 12/31/08
> 3/15/07 12/01/09
> 5/5/09 10/01/09
> 10/01/08
>
> How would i get a total count of items that started before 06/01/08 and
> ended after 06/01/09.
>
> it would be 1 in the example above.
>
> Thanks

 
Reply With Quote
 
Jacob Skaria
Guest
Posts: n/a
 
      20th Nov 2009
With start and end dates in cells c1 and D1 try the below

=SUMPRODUCT((A2:A10<C1)*(B2:B10>D1))

If this post helps click Yes
---------------
Jacob Skaria


"AccessNoviceButTrying" wrote:

> I have two columns which both contains dates. I want a count based on
> criteria from the dates. For example...
>
> Start Date End Date
> 01/01/08 12/31/08
> 3/15/07 12/01/09
> 5/5/09 10/01/09
> 10/01/08
>
> How would i get a total count of items that started before 06/01/08 and
> ended after 06/01/09.
>
> it would be 1 in the example above.
>
> Thanks

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting dates based on certain criteria steve_sr2 Microsoft Excel Worksheet Functions 5 1st Mar 2008 12:42 AM
Counting based on location & dates =?Utf-8?B?anByZW1hbg==?= Microsoft Excel Misc 1 28th Mar 2007 03:08 PM
RE: Counting based on location & dates =?Utf-8?B?TWlrZQ==?= Microsoft Excel Misc 0 28th Mar 2007 01:55 AM
RE: Counting based on location & dates =?Utf-8?B?anByZW1hbg==?= Microsoft Excel Misc 0 28th Mar 2007 01:14 AM
RE: Counting based on location & dates =?Utf-8?B?SHVtcGhyZXk=?= Microsoft Excel Misc 0 28th Mar 2007 12:10 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:06 PM.