Determine match based on if a date range falls within a larger date range :S

[lcaw]

Hi,
First time posting so hope I cover everything. I'm boggled by this request that has come across my desk.

I have a large data set of leave entries in a system. Example of two employees in this example

I am wanting a formula driven solution that will identify if the red rows have been 'overridden' by the green rows so I can mark and flag the red rows for removal at a later time.

For example, Row 7 (John Smith, Tentative, 8am 20th June - 5pm 23rd June) and Row 8 (John Smith, Tentative, 8am 27th June - 5pm 27th June) are both overridden by Row 6 (John Smith, Accepted, 20-27 June) because the dates are within the larger date range of F6:G6

Unfortunately we cannot rely on just removing all Tentative status rows as tentative can still become accepted when a new extract is taken. The key is to lookup all data and determine if there is a row belonging to the same employee with an 'Accepted' status and dates that are both before and after this entry.

So H7 needs to determine if the dates in H5:H6 falls anywhere between F:G with the criteria of matching the payroll number and status=Approved.

It may fall in the too hard basket unless one of you wizards can suggest a solution. Thanks in advance!

 

[dermie_72]

you could do a countifs in column G stating from G2 down.

=countifs(B:B,B2,C:C,C2,F:F,F2,G:G,G2)

Everything that is Tenative and has a 2 in the helper column has been overwritten by a green approved line.