Formulas to Indicate Date and Time Already Scheduled to Trigger Alert
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.
Best Answer
-
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)
Answers
-
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)
-
Thank you, Paul! That worked perfectly!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!