Find previous entry and return days since the previous date

[Dazzybeeguy]

I have a list of teams in Say Colum D2:D100 Team 1 & Team 2 ETC
I have the applicable date in A2:A100

In say cell E2:E100 I want a formula that looks for the team name then looks back to see the last time that same team name was shown and if it wasn't to say N/A and if it was to count the days between the two events.

So in the example shown in E11 it would return 3 days since last shift as Team 3 is showing as the 4th April and they were also showing on the 1st April.

What is the solution / formula that would work in this scenario - Thanks

A B C D E

Date	Day	Shift	Team	Days Since Last Shift
01/04/2025	Tuesday	Early	Team 1	N/A
01/04/2025	Tuesday	Late	Team 2	N/A
01/04/2025	Tuesday	Late	Team 3	N/A
02/04/2025	Wednesday	Early	Team 4	N/A
02/04/2025	Wednesday	Late	Team 5	N/A
02/04/2025	Wednesday	Late	Team 6	N/A
03/04/2025	Thursday	Early	Team 7	N/A
03/04/2025	Thursday	Late	Team 8	N/A
03/04/2025	Thursday	Late	Team 9	N/A
04/04/2025	Friday	Early	Team 3	3
04/04/2025	Friday	Late	Team 5	2
04/04/2025	Friday	Late	Team 1
05/04/2025	Saturday	Weekend	Team 8
06/04/2025	Sunday	Weekend	Team 10
07/04/2025	Monday	Early	Team 11
07/04/2025	Monday	Late	Team 2
07/04/2025	Monday	Late	Team 4
08/04/2025	Tuesday	Early	Team 9
08/04/2025	Tuesday	Late	Team 7
08/04/2025	Tuesday	Late	Team 3
09/04/2025	Wednesday	Early	Team 1
09/04/2025	Wednesday	Late	Team 4
09/04/2025	Wednesday	Late	Team 10
10/04/2025	Thursday	Early	Team 8
10/04/2025	Thursday	Late	Team 12
10/04/2025	Thursday	Late	Team 3

 

[hagia_sofia]

Hello, provided that the dates are sorted from the oldest to the newest, then maybe something like (inserted in E2 and copied down):

=IFERROR(A2-TAKE(FILTER(A$1:A1,D$1:D1=D2),-1),"N/A")

 

[Dazzybeeguy]

Hello, provided that the dates are sorted from the oldest to the newest, then maybe something like (inserted in E2 and copied down):

=IFERROR(A2-TAKE(FILTER(A$1:A1,D$1:D1=D2),-1),"N/A")

That seems to work just fine, Thanks