Number of Departure for Current Quarter in between 24 months

[netuser]

I have a table with Column A containing Entry date and Column B Departure Date (will be empty if no departure so should be ignored)

I need to find out for the Current Quarter How many departure I had who did not completed 24 months

So if I am in Quarter 2 it should look all departure dates from April 2016 to June 2016 and then calculate the number of months between entry and departure if it is <24 then it will Count

In this example below I have 2 departure in last 24 M in Q1 (row 4 and row 8) and 1 in Q2 Row 6 (note the Row 3 is not counted as it is more than 24 months)

Thanks

Excel 2010 32 bit
	A	B	C	D	E
1	Entry	Departure		Q1	2
2	2012-01-09	2014-01-13		Q2	1
3	2012-05-22	2016-04-11		Q3	0
4	2015-06-25	2016-02-28		Q4	0
5	2012-09-10	2014-05-09
6	2015-10-01	2016-05-13
7	2012-10-09	2015-02-01
8	2015-03-05	2016-01-18
Nb depart

 

[Green Crocodile]

Q1 =sumproduct(1*(month(b2:b8) < 4)*(datedif(a2:a8,b2:b8,"m") < 24)*(year(b2:b8)=year(today())))
q2 =sumproduct(1*(month(b2:b8) < 7)*(month(b2:b8) > 3)*(datedif(a2:a8,b2:b8,"m") < 24)*(year(b2:b8)=year(today())))
q3 =sumproduct(1*(month(b2:b8) < 10)*(month(b2:b8) > 6)*(datedif(a2:a8,b2:b8,"m") < 24)*(year(b2:b8)=year(today())))
q4 =sumproduct(1*(month(b2:b8) < 13)*(month(b2:b8) > 9)*(datedif(a2:a8,b2:b8,"m") < 24)*(year(b2:b8)=year(today())))

Please make sure you remove the spaces surrounding the < and > signs

 

[netuser]

Thanks for the above formulas, it is working great, but only when there is no empty departure date. As mentioned in my OP the departure date wont be always filled (most of the time). So your formula is not working in this case. Could you please look in to it ?

Excel 2010 32 bit
	D	E	F	G	H
1	Entry	Departure		Q1	#NOMBRE!
2	2012-01-09	2014-01-13		Q2
3	2012-05-22	2016-04-11		Q3
4	2015-06-25	2016-02-28		Q4
5	2012-09-10	2014-05-09
6	2015-10-01	2016-05-13
7	2012-10-09	2015-02-01
8	2015-03-05	2016-01-18
9	2015-10-01
10	2012-10-09
11	2015-03-05	2014-05-09
12	2012-05-22	2016-05-13
13	2015-06-25
14	2012-09-10
15	2015-10-01	2016-04-11
16	2012-10-09	2016-02-28
17	2015-03-05	2014-05-09
18	2015-10-01	2016-05-13
19	2012-10-09
Nb depart

Cell Formulas
Range	Formula
H1	=SUMPRODUCT(1*(MONTH(E2:E60)< 4)*(DATEDIF(D2:D60,E2:E60,"m")< 24)*(YEAR(E2:E60)=YEAR(TODAY())))

 

[Green Crocodile]

You can change the (DATEDIF(D2:D60,E2:E60,"m")<24) to this (DATEDIF(D2:D60,E2:E60+TODAY(),"M")-TODAY()<24).

 

[Green Crocodile]

You can change the (DATEDIF(D2:D60,E2:E60,"m")<24) to this (DATEDIF(D2:D60,E2:E60+TODAY(),"M")-TODAY()<24).

SORRY THIS FORMULA ACTUALLY DOESN'T WORK. I'll see how to modify it to count properly and get back to you.

 

[Green Crocodile]

You can change the (DATEDIF(D2:D60,E2:E60,"m")<24) to this (DATEDIF(D2:D60,E2:E60+TODAY(),"M")-TODAY()<24).

It should be changed to (DATEDIF(D2:D60,IF(E2:E60 < D2:D60,D2:D60+800,E2:E60),"M") < 24)

You would also need to enter it using Ctrl+Shift+Enter for it to work.

 

[netuser]

Thanks a lot, it is working great Just wondering what is 800 for ?

 

[Green Crocodile]

800 as in 800 DAYS. 800 days > than 24 months. I'm basically trying to return a FALSE if there is no departure date.

To do that I am having it check DEPARTURE column with ENTRY column and see if the value is less (a blank cells value = 0), if it is less the DATEDIF function will calculate the later date as the ENTRY DATE + 800, then the elapsed months between those two dates will be checked to see if they < than 24 months. 800 day difference is not (which returns a false and thats what we need). You can change the 800 to 1000 or 801 it doesnt matter as long as those amount of days are more than 24 months. Like 500 will not work as it is less than 24 months and will result in TRUE and hence be counted.

I hope this explanation made sense, I kind of lost myself while typing it out.

 

[netuser]

Thanks for explanation

Now I need to regroup all 4 formulas in 1 Cell so If you have any idea to do It quickly without a big 1 formulas with 4 IF, that will be welcome. Later I will need to put the result of this in a a Variable in VBA to use it for other things.

Thanks

 

[Green Crocodile]

Maybe something like this for the formula? It checks to see what quarter we are in today, and counts only the departure dates that are in the same quarter and year as today.

=SUMPRODUCT(1*(ROUNDUP(MONTH(B2:B8)/3,0)=ROUNDUP(MONTH(TODAY())/3,0))*(DATEDIF(A2:A8,IF(B2:B8 < A2:A8,A2:A8+800,B2:B8),"M") < 24)*(YEAR(B2:B8)=YEAR(TODAY())))

Ctrl shift enter