Date Range Conflict - Vacation Request Sheet

Paul Newcome

And the formula in the above screenshot is giving an #UNPARSEABLE error?? That is odd because it shows no signs of something being incorrect (cell/column refs/parenthesis are highlighted). I don't see any extra spaces or typos. All of the parenthesis and commas are in the right spots. There are no upper vs lower case issues anywhere. Arguments are in the correct order (<= vs =<).

Try logging out, clearing cookies and cache, then logging back in. If that doesn't work, we may need to pull in some additional help, because I really am not seeing where/why this formula would be producing any error at all.

Susan Worstall

Clearing the cookies seemed to help! Everything is working perfectly now. Thanks again for all of your help!

Paul Newcome

Great! Happy to help! 👍️

Ritika Bhalla

Hi @Paul Newcome

Thank you for the solution for flagging the overlap in date and time. Works like a charm!

Is there a way to also send email notifications to a 'contact' cell if a row is flagged and in the email specify the other rows that the conflict is with?

Paul Newcome

@Ritika Bhalla You would need to use a helper column and a JOIN/COLLECT formula to pull all overlapping row numbers/information into a single cell on the row that is triggering the notification.

Ritika Bhalla

@Paul Newcome Thank you for the response. I think I am very close to getting to what I need. I created a helper column using the following formula to get all the email ids of people that will have a conflict for a given date

JOIN(DISTINCT(COLLECT([Building Access For (Email ID)]:[Building Access For (Email ID)], [Visit Date]:[Visit Date], [Visit Date]@row)), " , ")

I now have something like this in my original sheet -

What I want in a separate sheet is -

I am not sure about what formula to use here. I intend to use this second sheet to then send email to people who have conflict for a given date. For example for the above formula I will send 1 email to person 1, person 2 & another to person 3, person 4

Thanks in advance!

Paul Newcome

Unfortunately this will not work as you expect it to. Multiple contacts cannot be populated into a single cell via formula.