So, currently I am using this sheet for a game, for a specific "team/regiment". How it works is when someone does a tryout, this is filled out in the 'Tryout Log' sheet, with the date and persons details. The persons details are then put onto the 'Member Data' sheet because on that they are assigned a unique ID number. This then means on the 'Roster' page, when you enter in the persons assigned unique ID, it auto populates the rest of their information.
There is a cell on the 'Roster' sheet called "Last Promotion' which returns a date.
Now, currently it looks at both the 'Tryout Log' and the 'Promo/Demo Log' sheet (the Promo/Demo Log is where promotions and demotions are logged.
The reason I have it looking at both sheets is because if someone is a Private who has just joined the regiment, they won't have had any promotion so their cooldown date will be the day they join the regiment (so the code looks at the Tryout Log sheet). For anyone above the rank of Private, they must've been promoted to be able to get to that rank and so it also looks at the 'Promo/Demo Log' to find the date they were last promoted.
My current issue is that when someone who was PREVIOUSLY in the regiment rejoins with a tryout, it seems to be getting an earlier date from a previous promotion the person has had.
To give a better example, a random user called James joins the regiment, gets multiple promotions then leaves. Months later then rejoins the regiment again as a Private. My code instead of getting their most recent tryout and putting that as their 'Last Promotion' date is instead for some reason getting the last promotion date for them from when they were in the regiment months ago.
How can I make it get the LAST date depending on whichever date is most recent between the Tryout Log and the Promo/Demo Log, so that if someone does rejoin who has a history of previous promotions, it'll just look at whichever was more recent and put that as the 'Last Promotion' date?
The code I have for my 'Last Promotion' date is
=IFS(ISBLANK(C88),"",MAX(INDEX((C88='Rank Change Log'!D$5:D)*'Rank Change Log'!C$5:C))=0,(MAX(INDEX((E88='Tryout Log'!F$5:F)*'Tryout Log'!C$5:C))),ISNUMBER(C88),MAX(INDEX((C88='Rank Change Log'!D$5:D)*'Rank Change Log'!C$5:C)))
Where C88 is the person's unique ID which is searches for on the 'Rank Change Log'. The reason for the
'Rank Change Log'!C$5:C))=0, part of the code is so that if they cannot find the person's unique ID in Promo/Demo Log, (which usually returns 0 which then ends up as a date ending in 1989?) it will then look in the Tryout Log sheet for a different reference (not the person's unique ID, but instead a different information called SteamID - This is the E88 reference in the code).
Please see below a copy of Google Sheet to hopefully help anyone who does try to solve the issue I'm having.