Find same date

McAfee Electric Mgr

I have an employee request for time off sheet. One column has the date the employee has requested off, another has the date the employee is set to return to work and a third column is "Other Employees off at the same time." Is there a formula that will search the entire sheet of dates of leave and dates of return to let me know who else is going to be off during that same time frame?

Paul Newcome

Try somethign like this:

=IF(COUNTIFS([Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row) > 1, 1)

This will output a 1 on any rows that have overlapping dates.

Paul Newcome

You can use a JOIN/COLLECT for that.

=IF(COUNTIFS([Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row) > 1, JOIN(COLLECT([Name Column]:[Name Column], [Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row), ", "))

Paul Newcome

Try somethign like this:

=IF(COUNTIFS([Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row) > 1, 1)

This will output a 1 on any rows that have overlapping dates.

McAfee Electric Mgr

That certainly helps! Thank you! I at least now know to look for another employee. It would be ideal if it would populate the name of the other employee that shares the same dates but this does help. Thank you!

Paul Newcome

You can use a JOIN/COLLECT for that.

=IF(COUNTIFS([Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row) > 1, JOIN(COLLECT([Name Column]:[Name Column], [Leave Date]:[Leave Date], @cell <= [Return Date]@row, [Return Date]:[Return Date], @cell >= [Leave Date]@row), ", "))

McAfee Electric Mgr

@Paul Newcome That is perfect! Thank you very much!

Paul Newcome

Happy to help. 👍️