Find previous entry and return days since the previous date

Dazzybeeguy

Board Regular
Joined
Jan 6, 2022
Messages
123
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

DateDayShiftTeamDays Since Last Shift
01/04/2025TuesdayEarlyTeam 1N/A
01/04/2025TuesdayLateTeam 2N/A
01/04/2025TuesdayLateTeam 3N/A
02/04/2025WednesdayEarlyTeam 4N/A
02/04/2025WednesdayLateTeam 5N/A
02/04/2025WednesdayLateTeam 6N/A
03/04/2025ThursdayEarlyTeam 7N/A
03/04/2025ThursdayLateTeam 8N/A
03/04/2025ThursdayLateTeam 9N/A
04/04/2025FridayEarlyTeam 33
04/04/2025FridayLateTeam 52
04/04/2025FridayLateTeam 1
05/04/2025SaturdayWeekendTeam 8
06/04/2025SundayWeekendTeam 10
07/04/2025MondayEarlyTeam 11
07/04/2025MondayLateTeam 2
07/04/2025MondayLateTeam 4
08/04/2025TuesdayEarlyTeam 9
08/04/2025TuesdayLateTeam 7
08/04/2025TuesdayLateTeam 3
09/04/2025WednesdayEarlyTeam 1
09/04/2025WednesdayLateTeam 4
09/04/2025WednesdayLateTeam 10
10/04/2025ThursdayEarlyTeam 8
10/04/2025ThursdayLateTeam 12
10/04/2025ThursdayLateTeam 3
 
Hello, provided that the dates are sorted from the oldest to the newest, then maybe something like (inserted in E2 and copied down):

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

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top