Count If Formula comparing dates

Question

Hi,

I'm looking for a simple formula which will compare two dates in two different cells and depending on which date meets the condition, it is counted.

So for example, below, I only want to ATP1, if the date is less than or equal to 31/05 and ATP2 date is greater than 31/05/10. But I want ATP2 counted if it is less than 31/05.

Project             ATP1 Date              ATP2 Date

AB123             15/03/2010             02/06/2010

AB234             14/03/2010             05/05/2010

AB345             21/05/2010             31/05/2010

Can anyone help please? If it's not clear, let me know. It would be really appreciated.

Thanks, M

Answer 1

A bit confused by what you want. Are you looking to count the number of occasion where the date under ATP1 is less than or equal to 31/5 and ATP2 is greater than 31/5/2010. And separately you want to count the number of times ATP2 is less than 31/5/2010?

If so, see this whether work for you.

to count scenario 1 =SUMPRODUCT(--(E2:E4<=DATE(2010,5,31)),--(F2:F4>DATE(2010,5,31)))

for scenario 2 =COUNTIF(F2:F4,"<31/5/2010")

Change the range as required

Answer 2

Hi,

This, to me at least isn't clear.  If we take your posted data

Project             ATP1 Date              ATP2 Date

AB123             15/03/2010             02/06/2010 here we count ATP1 date

AB234             14/03/2010             05/05/2010 Here we count ATP2 date

AB345             21/05/2010             31/05/2010 here we count ATP2 date

So having counted then we get 3 and as far as I can see we will always get 3 because we are counting a date on each row!

Please clarify

---

If this post answers your question, please mark it as the Answer.

Answer 3

Hi,

I supose that ATP1 is in column B and ATP2 in column C so to count the first scenario use

=sumproduct(--($B$1:$B$1000<=Date(2010,5,31)),--($C$1:$C$1000>date(2010,5,31)))

for your 2nd sceneario

=sumproduct(--($B$1:$B$1000<=Date(2010,5,31)),--($C$1:$C$1000<=date(2010,5,31)))

Answer 4

Hi,

What I'm after in the above example is to be able to count either the ATP1 or ATP2 date depending on the date itself. So if ATP1 is less than or equal to 31/05 AND ATP2 is greater than 31/05, then I want the corresponding date for ATP1 to be counted. But if both ATP1 and ATP2 dates happen to be less than or equal to 31/05, then it's the ATP2 date that needs to be counted.

The first scenario formula will only count the number of dates where ATP1 is less than 31/05 and ATP2 is greater. But it won't count in the same cell the second scenario.

So I guess what I'm after is a combination of the two scenarios into one formula. Is this possible or is it too complicated? Please let me know if i'm still not clear.

Thanks

Answer 5

Hi,.

could you give an example of your output, as per your description you will count ATP1 if the date is less or equal to 31/05 and ATP2 greater than 31/5, however you will count ATP2 if it is less than 31/5?, in that case will not count ATP1, doesn't it?