Count consecutive dates

[cristinutika]

Hello Everyone,

I would like to ask for your support regarding how can I count any consecutive dates for each employee based on Unique report number and Destination.
Basically, I want to count how many consecutive dates a employee stayed in a same location.
Is this possible to do using an excel formula?

Thank you in advance!

 

[CA_Punit]

A slightly long formula but worth checking a while

Book1
	A	B	C	D	E	F
2	Employees	Report Number	Destination	Date
3	Cristiana		India	01/01/2020		4.00
4	Cristiana		India	02/01/2020
5	Cristiana		India	03/01/2020
6	Cristiana		India	04/01/2020
7	John		Russia	06/01/2020		1.00
8	Cristiana		India	07/01/2020		3.00
9	Cristiana		India	08/01/2020
10	Cristiana		India	09/01/2020
11	Punit		India	10/01/2020		1.00
12	Cristiana		India	11/01/2020		2.00
13	Cristiana		India	12/01/2020
14	Ingrid		India	01/01/2020		1.00
Sheet1

Cell Formulas
Range	Formula
D4:D6,D8:D13	D4	=D3+1
F3:F14	F3	=IFERROR(IF(A3=A2,"",SUM(--((IF(A3:$A$14=A3,D3:$D$14)-(D3+(ROW(D3:$D$14)-ROW(D3))))=0)*IF((ROW(D3:$D$14)-ROW(D3)+1)>MATCH(0,--(IF(A3:$A$14=A3,D3:$D$14)),0),FALSE,TRUE))),SUM(--((IF(A3:$A$14=A3,D3:$D$14)-(D3+(ROW(D3:$D$14)-ROW(D3))))=0)))

 

[CA_Punit]

Hi @jasonb75

I figure out your answer is resulting only 1 .

Book1
	A	B	C	D	E	F
1	Employees	Report Number	Destination	Date	JASON Answer	Punit Answer
2	Cristiana		India	01/01/2020		4.00
3	Cristiana		India	02/01/2020
4	Cristiana		India	03/01/2020
5	Cristiana		India	04/01/2020	1
6	John		Russia	06/01/2020	1	1.00
7	Cristiana		India	07/01/2020		3.00
8	Cristiana		India	08/01/2020
9	Cristiana		India	09/01/2020	1
10	Punit		India	10/01/2020	1	1.00
11	Cristiana		India	11/01/2020		2.00
12	Cristiana		India	12/01/2020	1
13	Ingrid		India	01/01/2020	1	1.00
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=IF(AND(A3=A2,B3=B2,C3=C2,D3=(D2+1)),"",ROW(E2)-IFERROR(MATCH(1E+100,D$1:D1),1))
F2:F13	F2	=IFERROR(IF(A2=A1,"",SUM(--((IF(A2:$A$13=A2,D2:$D$13)-(D2+(ROW(D2:$D$13)-ROW(D2))))=0)*IF((ROW(D2:$D$13)-ROW(D2)+1)>MATCH(0,--(IF(A2:$A$13=A2,D2:$D$13)),0),FALSE,TRUE))),SUM(--((IF(A2:$A$13=A2,D2:$D$13)-(D2+(ROW(D2:$D$13)-ROW(D2))))=0)))
D3:D5,D7:D12	D3	=D2+1

Hi
@cristinutika

Is that what you required.? If it is so than I suppose i missed on some criteria

 

[jasonb75]

Hi @jasonb75

I figure out your answer is resulting only 1 .

So did I, the corrected formula is in post 9.

 

[CA_Punit]

Oh I got it,

Amazing Thanks i will go through it.