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

 

[Skyybot]

Like this?

Book1
	A	B
1	7/12/2023
2
3	4/20/2022	448
4
5	2/12/2023	298
6
7	1/8/2018	1861
8
9	2/20/2022	1504
10
11	1/20/2022	31
12
13	7/10/2023	536
Sheet1

Cell Formulas
Range	Formula
B3,B13,B11,B9,B7,B5	B3	=IF(A1>A3, A1-A3,A3-A1)

 

[Noxqss38242]

No, if put in chronological order, there would be 1473 days between 1/8/2018 and 1/20/2022 which would be in chronological order. Then 31 days between the next set of 1/20/2022 and 2/20/2022, and so on...

1/8/2018​
1/20/2022​	1473​
2/20/2022​	31​
4/20/2022​	59​
2/12/2023​	298​
7/10/2023​	148​
7/12/2023​	2​

But I need it to calculate automatically without rearranging the dates....was hoping adding a helper column might help.

 

[Skyybot]

Yeah. Take a closer look at my example. They are not in chronological order and the proper count of days is correct. 448 days between 7/12/2023 and 4/20/2022. 298 days between 4/20/2022 and 2/12/2023...and so on. I'm not sure if I'm understanding correctly.

 

[Skyybot]

Yeah. Take a closer look at my example. They are not in chronological order and the proper count of days is correct. 448 days between 7/12/2023 and 4/20/2022. 298 days between 4/20/2022 and 2/12/2023...and so on. I'm not sure if I'm understanding correctly.

Ahhhh. I see what you are saying @Noxqss38242 . Stand by.

 

[Skyybot]

Book1
	A	B
1	1/20/2022
2	1/8/2018	1473
3	1/6/1998	7307
4	3/4/2026	10284
5	2/1/2016	3684
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=ABS(DAYS(A1,A2))

 

[Noxqss38242]

I'm needing it to calculate like IF it was in chronological order.

It is for accidents that occurred and finding out how many days since the last accident but the data is entered radomly.

So, the formula needs to be able to tell, what is the oldest date and the elapsed time it took since the next chronological next date.

The "answers" to the math is what I posted in post # 3 but I had to put them in chronological order to get that, I need a formula that I don't have to sort the data first which is why I thought I needed to have a helper column. In my original post, #1 is the newest date and #7 is the oldest date. So I need a formula that maybe says for it to search the helper column for 7, grab the date next to it, then look for 6 and minus the date that matches for 6.

My first example I had the formula (that doesn't work) All the way to 1, search 1 date and minus 2 date, 2 date minus 3 date, 3 date minus 4 date, so on and so forth....since the helper column says what order they are chronologically.

I hope I'm not confusing it more....

 

[Skyybot]

I'm needing it to calculate like IF it was in chronological order.

It is for accidents that occurred and finding out how many days since the last accident but the data is entered radomly.

So, the formula needs to be able to tell, what is the oldest date and the elapsed time it took since the next chronological next date.

The "answers" to the math is what I posted in post # 3 but I had to put them in chronological order to get that, I need a formula that I don't have to sort the data first which is why I thought I needed to have a helper column. In my original post, #1 is the newest date and #7 is the oldest date. So I need a formula that maybe says for it to search the helper column for 7, grab the date next to it, then look for 6 and minus the date that matches for 6.

My first example I had the formula (that doesn't work) All the way to 1, search 1 date and minus 2 date, 2 date minus 3 date, 3 date minus 4 date, so on and so forth....since the helper column says what order they are chronologically.

I hope I'm not confusing it more....

Did you try post #6?

 

[Noxqss38242]

With that formula, it shows:

I put that formula in but it doesn't work the way it should because it should be:

Since it was only 150 days between 7/12/2023 and the next chronological date of 2/12/2023 and so on...

 

[Skyybot]

Since it was only 150 days between 7/12/2023 and the next chronological date of 2/12/2023 and so on...

Chronologically, 7/12/2023 is after 2/12/2023. And, if you're sorting "chronologically", then use Sort and include extra data.