# Find same date

Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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), ", "))

• ✭✭✭✭✭✭
Options

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.

• Options

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!

• ✭✭✭✭✭✭
Options

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), ", "))

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!