Converting list of date into a string response to match condition in formula


I have two sheets, one sheet has the employee name, start date, end date, total days and the aggregation column. The aggregation column separates the date range from the start date to end date.

the Aggregation formula is; =JOIN(COLLECT({Employee Leave (Part 2) - Date}, {Employee Leave (Part 2) - Date}, >=[Start Date]@row, {Employee Leave (Part 2) - Date}, <=[End Date]@row), ", ")

and another sheet has the date, and employee on leave where I wanted the name of employee will be shown for the date that they requested to have an on-leave

the formula of Employee on leave is; =JOIN(COLLECT({Employees Leave (Part 1) - Name}, {Employees Leave (Part 1) - Agg}, HAS(@cell, Date@row)), " , ")

How to make Anna's name exist in Employee On Leave column from date 12/26/23 to 12/29/23? Which I wanted the result should like screenshot below

currently, I can get James since he only applied for one day only for leave

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!