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

lcaw

New Member
Joined
Apr 16, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!

1713249320757.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
you could do a countifs in column G stating from G2 down.
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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