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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!