Help with formula

[Noxqss38242]

I'm trying to find days elapsed based on dates but the dates are not chronological.
I tried putting in a helper column by using "rank" function.
Then tried to say if rank is greater than 1, index/match to pull the date and subtract current rank from previous rank's date but it errors out....

Qualifications.xlsx
	C	D	E
31	7/12/2023	1
32
33	4/20/2022	4	#VALUE!
34
35	2/12/2023	3
36
37	1/8/2018	7
38
39	2/20/2022	5
40
41	1/20/2022	6
42
43	7/10/2023	2
Sheet1

Cell Formulas
Range	Formula
D31,D43,D41,D39,D37,D35,D33	D31	=IF(ISBLANK(C31),"",RANK(C31,($C$31:$C$72),0))
E31,E33	E31	=IF(D31>1,INDEX($C$31:$C$72,MATCH(D31,$D$31:$D$72,0)-INDEX($C$31:$C$72,MATCH(D31-1,$D$31:$D$72,0))),"")
E40	E40	=IF(ISBLANK(C40),"",(ABS(C40-C38)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A31:AF72	Expression	=MOD(ROW(),2)=0	text	NO

 

[Noxqss38242]

I won't be the one using the sheet, otherwise I would, people that are NOT very experienced will be which is why I reached out for help....otherwise, yes...simple sort and apply....but that isn't the case which is why I was using a helper column.

Is there not a way to say rank 7's date next cell over MINUS rank 6's date next cell over?

That way they wouldn't have to sort, as it wouldn't matter since the helper column too would automatically rank them?

 

[Fluff]

Is this what you want

Fluff.xlsm
	C	D	E
31	12/07/2023	1	2
32
33	20/04/2022	4	59
34
35	12/02/2023	3	298
36
37	08/01/2018	7
38
39	20/02/2022	5	31
40
41	20/01/2022	6	1473
42
43	10/07/2023	2	148
Checks

Cell Formulas
Range	Formula
D31,D43,D41,D39,D37,D35,D33	D31	=IF(ISBLANK(C31),"",RANK(C31,($C$31:$C$72),0))
E31:E43	E31	=IFERROR(C31-AGGREGATE(14,6,$C$31:$C$43/($C$31:$C$43<C31)/($C$31:$C$43<>""),1),"")

 

[Noxqss38242]

SWEET! Much love!

Aggregate huh? I will definitely need to look into what all that can do....

They should have "mrexcel" links for functions, with everyone being able to share what they found the function can do for them....then the moderators can decide which ones are best, lock them and then shut down further posts to it. Would be quite a task at first....but one wanting to learn more about excel without just "youtubing" it....would be nice...

 

[Fluff]

Glad we could help & thanks for the feedback.