3

Hello, 

 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!

Comments

Andree_Stara

Hi Matt,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

In reply to by Andrée Starå

Absolutely! Thank you for responding. There's no confidential information here, so I can share, and I have attached a screenshot. Basically, I'm looking for a formula that will check to make sure that, for a given day at a given time, we're not scheduling one conference line in more than one class. I've created the conflict column on the right, and want it to automatically flag when the formula identifies a conflict. 

I can write a formula that flags when the date appears more than once, but I'm not sure how to account for an overlap in start and end time. For example a conference line could be used from 9 AM to 11 AM for one class, and then again from 12:00 PM to 2:00 PM for a different class, but not at the same time for two different classes.