Flagging a Date and Time Overlap

MattSederquist

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!

Andrée Starå

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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

MattSederquist

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. 

Andrée Starå

Happy to help!

Can you maybe share the sheet or a copy of it? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Ann Marie Rakovic

Hi, was this case scenario resolved? I have a very similar need and would like to learn how overlaps for time of day for a specific event/task can easily be identified. Many thanks. Ann Marie

Paul Newcome

@Ann Marie Rakovic What format are your times? Are they 12 or 24 hour? Are you using a colon?

Becca Millock

@Paul Newcome Hi Paul - I am working with Ann Marie on this issue. Thank you for responding so quickly. Our times are currently 12 hour and we are using a colon (ex: 12-2:00pm, 4:30-5:30pm). We have flexibility in our formatting to switch to a 24 hour format that drops the colon (e.g., 12:30pm to 1230).

Paul Newcome

@Ann Marie Rakovic & @Becca Millock

Would you need to look across multiple days? Is there a possibility of something along the lines of

2 Feb. 20 @ 9:00pm - 3 Feb. 20 @ 2:00am

3 Feb. 20 @ 1:00am - 3 Feb. 20 @ 4:00am

These two rows overlap and cross over midnight into a different day.

Are you able to provide more detail as to your current setup/process and provide a screenshot or two with sensitive/confidential information blocked, removed, or replaced with "dummy data" as needed?

This will help with the context and will help determine exactly how complicated this does or does not need to be.

Becca Millock

@Paul Newcome We will not need to look across multiple days. I've attached an example of our current setup/process for recording dates and times. Examples of issues we would like to flag are highlighted.

Calendar of Events_Test.xlsx

Paul Newcome

Ok. I will take a look and get back to you. What are the chances you could break down the time into a start time column and an end time column?

Becca Millock

Thanks Paul! Yes, we can do a start and end time column. Updated example with this change attached.

Calendar of Events_Test.xlsx

Paul Newcome

Excellent! Let me throw a few things together, and I'll get back to you with a possible solution!

Paul Newcome

@Ann Marie Rakovic & @Becca Millock

HERE is a published sheet that contains a possible solution. The data in the sheet is copy/pasted from your last excel example you provided.

You will see that the flags in the sheet match the highlighted rows for overlap in your excel example. Below the testing rows, there are three more rows with a column name and corresponding formula. The Start and Finish columns are used to convert to corresponding time columns into numerical values that can be used to look for overlap.

The Overlap column currently checks for the same Date and Virtual License before looking at the time overlaps.

Becca Millock

@Paul Newcome Paul - We cannot thank you enough for the time and effort you spent helping us. This is exactly what we were looking for. We also appreciate the explanation of the solution you provided. It was clear and straightforward, which allowed us to easily apply it to our data. Many thanks, Becca and Ann Marie

Paul Newcome

@Ann Marie Rakovic & @Becca Millock

Happy to help! 👍️

@MattSederquist

Were you able to find a working solution? If not, does mine suit your needs? I am not sure if it will since you are not using colons in your screenshot and my solution for Ann Marie and Becca requires the colon to work properly.

Paul Newcome

@Megha Brahmbhatt It looks like you have an extra closing parenthesis after your first range in the COUNTIFS.

=IF(COUNTIFS(Site:Site)..........

Remove that and see if it works for you.