How to flag duplicate dates within a range
Hi everyone, I'm attempting to build an annual leave tracker for my team. The current build is the employee completes a form with Start and End dates > this updates on a Request Log for managers to Approve / Decline > once Approved, copied to a master log and a separate Tracker sheet keeps a list of all employees, their starting leave total, their days booked and what's remaining for the financial year.
However, there is an issue I'm having difficulty with - I can't find a way for the Request Log to flag entries in which a date is repeated in two or more rows. For example, in the screenshot below:
The amber dots represent dates an employee would have 'double-booked' a date by mistake. I'm trying to work out a way for Smartsheet to recognise dates within the Start and End Date columns and flag up in whatever way possible if a date is duplicated in rows. So in the above example, Jane Doe has requested 21/02/2025 off twice, as has John Smith the 10/03/2025. This would then enable me at a glance to see and then I could inform the employee/their manager if this were to occur.
Can anyone help me with this problem?
Best Answer
-
Putting this in a flag type column should do the trick:
=IF(COUNTIFS([Annual Leave Start Date]:[Annual Leave Start Date], @cell <= [Annual Leave End Date]@row, [Annual Leave End Date]:[Annual Leave End Date], @cell >= [Annual Leave Start Date]@row) > 1, 1)
Answers
-
Putting this in a flag type column should do the trick:
=IF(COUNTIFS([Annual Leave Start Date]:[Annual Leave Start Date], @cell <= [Annual Leave End Date]@row, [Annual Leave End Date]:[Annual Leave End Date], @cell >= [Annual Leave Start Date]@row) > 1, 1)
-
Thank you! That worked :)
Help Article Resources
Categories
Check out the Formula Handbook template!