Flagging a Date and Time Overlap

edited 12/09/19 in Formulas and Functions


 I have a situation where we teach virtual classes that are each approximately 2- 4 hours in length, start and end at various times each day, and each requires a unique conference phone line dedicated to the class. We can teach as many as 6 to 8 classes in a day, but only have 4 conference lines.

I'm trying to write a formula that checks to make sure that, at any given time on any given day, we aren't double booking a conference line. For each class, I have the following columns: Date, Start Time, End Time, and Conference Line. I've also added a column at the right that will display a flag if there is a scheduling conflict with a particular conference line. I've tried using an AND(IF formula, but I'm not the best with complicated formulas and was having some trouble formatting it correctly (specifically, how to make it first check if dates are duplicated, then have it check the row with the duplicate date to check for a time overlap, then check that same row to see if the conference lines are duplicated). 

Thank you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!