Don't count cell B if data in Cell I is the same month - Dashboard

[kryshawk]

I'm building an interactive dashboard with slicers and pivot tables. My challenge is that I have two columns with dates. I need to calculate how many dates in the second column have a future date in the next month based on the referral date. Example below. What I'm trying to achieve is to show that if I do a slicer for the month of February, it will show the number 1 in future start date in my cell on my dashboard. In April it will show 0 because they both started in the same month. May would show 1. So essentially I need assistance on how I "don't count cell A if its the same month as cell B" using pivot/slicers. Is it possible?

Referral Date	Assigned Start Date
02/02/17	03/03/17
04/04/17	04/20/17
04/05/17	04/20/17
05/01/17	06/20/17

<tbody>
</tbody>

 

[amitp]

If i correctly interpret your data , is that what you want ?

	A	B	C
1	Referral Date	Assigned Start Date	Result
2	02 Feb 17	03 Mar 17	1
3	04 Apr 17	20 Apr 17	0
4	05 Apr 17	20 Apr 17	0
5	01 May 17	20 Jun 17	1

<tbody>
</tbody>

if yes, then formula for C2 --> =COUNTIFS($B$2:$B$5,">="&(DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))-DAY(DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))+1,$B$2:$B$5,"<="&(EOMONTH(EDATE(A2,1),0)))

thanks

 

[kryshawk]

Yes. So my next question would be does it have to be a formula or is there a way that it can be a pivot table?

 

[amitp]

I am not sure if this can be achieved via pivot table, have not tried yet. May be possible by including calculated fields in pivot, will have to wait for the expert members to reply on this.

 

[Guru008]

Hi,

Paste this formula in C =IF(TEXT(A2,"mm-yy")=TEXT(B2,"mm-yy"),0,1)

and then sum the Column C in pivot table, it will sum all the values where month is equal