CODEWITHSUNDEEP
excelexcel-formula
user23336286
user23336286

Reputation: 3

Excel formula to identify gaps in overlapping date ranges

I'm looking for a formula that will allow me to easily see if there are any gaps in dates across two different date ranges.

In this example, I need to check that the dates given in Columns C and D (their contract dates) cover the time period given in columns A to B (the actual dates worked)

enter image description here

I have seen there is a 'gap' formula but that only seems to work if I have two columns of dates, not two different date ranges going across 4 columns

Upvotes: 0

Views: 288

Answers (1)

Ike
Ike

Reputation: 13064

You can use this formula

=IF((A2:A5>=C2:C5)*(B2:B5<=D2:D5),"OK", "Not OK")

It spills to all four rows.

Using * is like and AND.

enter image description here

Upvotes: 0

Related Questions