*** HELP *** Checking for time overlap of multiple records based on criteria

[mpappis]

Hello,

This may be one of those "impossibles", but I am stuck on where to even start with this formula.

I am trying to use EXCEL to create a small resource schedule. Each "row' can be considered an activity or a shift. If for a particular "resource name" the resource has been double booked (same day, same overlap) i would like to see a "conflict" or "warning" as the output.

columns:
resource name, Start Time, End Time, Conflict?
frank, 6/14/2010 8:00 AM, 6/14/2010 2:00 PM, No
dan, 6/14/2010 10:00 AM, 6/14/2010 5:00 PM, No
Dan, 6/15/2010 11:00 AM, 6/15/2010 3:00 PM, No
Shelly, 6/15/2010 8:00 AM, 6/14/2010 1:00 PM, No

New rows gets created as:
frank, 6/14/2010 1:30 PM, 6/14/2010 4:00 PM, Yes
frank, 6/14/2010 5:00 AM, 6/14/2010 8:15 AM, Yes
frank, 6/14/2010 12:00 PM, 6/14/2010 1:00 PM, Yes

additionally, the very first row would change "yes"..

so what formula do I put in each row for "conflict" to get the appropriate response.

sincerely....

 

[mpappis]

bump..... i still need a solution to this problem if anyone has any ideas....

is using dget in some fashion a possibility?

 

[shemayisroel]

Hi & Welcome,

Based on your sample data, how have you deemed Frank as being a conflict? Are the checks for deeming a duplication based on the following?

A) Name

B) Date

C) Start Time

D) End Time

?

 

[AlphaFrog]

Try something like this in D2 and copy it down column D

=IF(ISBLANK(A2),"",IF(SUMPRODUCT(($A$2:$A$100=A2)*(($B$2:$B$100<=B2)*($C$2:$C$100>=B2)+($B$2:$B$100<=C2)*($C$2:$C$100>=C2)))>2,"Yes","No"))

 

[shemayisroel]

Try something like this in D2 and copy it down column D

=IF(ISBLANK(A2),"",IF(SUMPRODUCT(($A$2:$A$100=A2)*(($B$2:$B$100<=B2)*($C$2:$C$100>=B2)+($B$2:$B$100<=C2)*($C$2:$C$100>=C2)))>2,"Yes","No"))

Dont think that will work...

mpappis - will there be any instances where the start and end dates are different I.E going past midnight?

 

[mpappis]

yes, it should be able to understand the whole date..... so shifts can go from 1 day, at AM, all the way until the next day am.... or start at pm and go to the next day am as well.

and to your early question, only frank is considered a duplicate, because each row represent a shift, and he has shifts where the times overlap with his other shifts on the same day and time, so Frank is conflict with himself.

i am going to try alphadog's solution now....

 

[mpappis]

hi alphadog, your syntax worked in my sheet, unfortunately I am not getting yes's for any of Frank's rows..... I haven't used sumproduct before, but I think you are on the right track.... did you get yes's for frank in your sheet?

 

[AlphaFrog]

Yes. It worked for me.

Make sure the date-times in columns B and C are actual date-time values that excel recognizes and not just text that may just "look" like a date-time.

 

[mpappis]

amazing. it works (when i copied and pasted my own example, I formatted the time columns, but you were right it didn't fully format it.... but i fixed that and voila..

. can you please explain your formula a little so I understand it?

I gave a very simple example that I need to apply in a larger sheet. I basically understand how sumproduct works, but still need help with what you are doing in your formula. it looks like your sumproduct is only using 1 large array, since there are no "," commas in between it. what does the "*" .....any explanation will help

 

[shemayisroel]

amazing. it works (when i copied and pasted my own example, I formatted the time columns, but you were right it didn't fully format it.... but i fixed that and voila..

. can you please explain your formula a little so I understand it?

I gave a very simple example that I need to apply in a larger sheet. I basically understand how sumproduct works, but still need help with what you are doing in your formula. it looks like your sumproduct is only using 1 large array, since there are no "," commas in between it. what does the "*" .....any explanation will help

Thsi will help you understand SUMPRODUCT

http://www.xldynamic.com/source/xld.SUMPRODUCT.html