Need assistance with collecting and then joining

Need assistance with collecting and then joining


I have a test master grid sheet where dates of leave (and different types of leave) are collected through a form.

Formula used for collecting dates together is:

=IF([End Date]@row <= [Start Date]@row, [Start Date]@row + "", JOIN([Start Date]@row:[End Date]@row, " - "))

I have a 2nd sheet from which I want to collect information from the master but for specific dates. For instance, only collect the dates together if the Start Date is between 6/1/20 and 6/30/20. I assume this is a combination of collect and join, but I have no idea which order to nest.

This is the formula I've determined so far, but now need to add the piece that collects for the specified dates. This cannot be a report because I will automate approvals on the data. I will substitute "June...) with the column-specific name. Screenshot of destination sheet below.

=JOIN(COLLECT({June Dates Together}, {June Email}, [Email/Contact]@row, {June A/L}, >1), " , ")

See Rebecca Panaccione on the bottom row. The above formula works to collect and combine all the dates from the Master as specific to Annual leave. That part works. I would like to fine tune this to collect the information for only the dates that start with 6/1/2020. Carry over is okay. It's the start date that drives the data.

As always, thank you so much for your time. You make me look smart!

Rebecca Panaccione

Best Answer


Sign In or Register to comment.