filter inactive records

G

glctech

I have a list of vehicles dispatched by date and time. Columns ar
labeled date, time, vehicle id, origin and destination. I want t
list/filter the vehicles that have no entries for the last seven day
but only want the most recent record (older than seven days) and I wan
the entire row so I know where the vehicle was last. I can custo
filter for a date range that is less than a given variable but can'
figure out how write the IF to drop those record that have a mor
recent date entry
 
D

Debra Dalgleish

You could add a column to the list, and calculate the old dates there.
For example, with dates in column A and vehicles in column C, enter the
following array formula in row 2:

=IF(AND(A2<TODAY()-6,A2=MAX(IF($C$2:$C$200=C2,$A$2:$A$200))),A2,"")

then hold Ctrl+Shift, and press the Enter key.

Copy the formula down to the last row of data.

Filter for NonBlank rows
 
Top