Updating a timestamp if a previous submission is older than a newer submission

Question

So Im working on a gaming community and have a "Roll Call" form they fill out each day they play with us so we can track if they are active. This form comes with name, the unit they are in and the timestamp when it was submitted (only important pieces). It is all sent to a google sheets page and has a google forms page linked.

What I am trying to do is to have it so in their roster, the most recent roll call will update a cell next to their name with the date submitted, while also changing the colour of the cell to indicate if it fits within a certain time period.

Currently, I have tried =IF('Roll Call'!A3:A<TODAY(-3)AND('Roll Call'!B3:B=D6),"ACTIVE","Inactive")

A3:A is all timestamps logged, B3:B is the name submitted and D6 on the sheet is the name of the person. Eventually I want it to be that if submitted within 3 days they are active, if submitted within 3-6 days its inactive and after 7 days they are "AWOL".

For now, Im not fussed about the changing colours based on activity, I just want it to auto update the last submitted timestamp in Day/Month/Year format based on the name of the person submitted. Anyone able to direct me and help me in any way?

Main Roster Page

Raw data roll call page

Answer 1

try:

=IF(IFNA(FILTER('RAW - ROLL CALL'!A2:A, 'RAW - ROLL CALL'!A2:A<TODAY()-3, 'RAW - ROLL CALL'!B2:B=E6))="", 
 "ACTIVE", "INACTIVE")