Formulas to Indicate Date and Time Already Scheduled to Trigger Alert

Lori Flanigan

I am trying to create a form for people to use to sign up for a specific date and time (e.g., 12/12/22 @ 11:30 a.m.) to pick up some work equipment.

I created a (1) sign up sheet to capture the form data and (2) a separate sheet containing all dates and times to be scheduled.

In the dates and times sheet, I have a column (Taken) in which I wrote a COUNTIFS formula to indicate the number of people signed up for that date and time; however, only one person can be signed up for each date and time. I want to send an alert to anyone who signs up for a time already taken, but am not sure how to limit the Taken column to only the first person who scheduled that time or how to trigger the alert to send to anyone who schedules a time already scheduled. I created the Unavailable column in the sign up sheet so I could send the alert from that sheet but don't know what formula would work to flag when someone (fx@bing.com) signs up for a time and date already scheduled.

I'm certainly open to ideas to change how I've set things up.

Any help is appreciated!

Lori F.

Paul Newcome

I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then you can use a formula in the flag column to flag the rows that have signed up for something that is already taken like so:

=IF(COUNTIFS([Date and Times]:[Dates and Times], @cell = [Dates and Times]@row, Auto:Auto, @cell<= Auto@row)> 1, 1)

Paul Newcome

I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then you can use a formula in the flag column to flag the rows that have signed up for something that is already taken like so:

=IF(COUNTIFS([Date and Times]:[Dates and Times], @cell = [Dates and Times]@row, Auto:Auto, @cell<= Auto@row)> 1, 1)

Lori Flanigan

Thank you, Paul! That worked perfectly!

Paul Newcome

Happy to help. 👍️