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

Sarah Othman

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

Paul Newcome

Change the Aggregation column to a multi-select dropdown and adjust the delimiter in the formula populating that column from commas to line breaks.

CHAR(10)

Paul Newcome

Change the Aggregation column to a multi-select dropdown and adjust the delimiter in the formula populating that column from commas to line breaks.

CHAR(10)

Sarah Othman

Hi @Paul Newcome

thanks! it's working! but can I know why we use CHAR(10)?

Paul Newcome

CHAR(10) is a line break which is the "built in" delimiter in a multi-select dropdown type column.

Sarah Othman

Ahhh thanks @Paul Newcome .

I also wanted to count the contact name list if there is multiple name in the cell. I tried to use this formula, =COUNTIF([Employees On Leave]@row, HAS({Employee Name}, @cell)) but it's not working.

if there is "Anna, James" in one cell, it counts as 1. Can you help me?

Thanks in advance