Complicated Audit Formula

nickrosen28

Hi everyone,

I am re-creating a process that audits people entering a lounge, and if we can charge them.

If they enter in once they can have a blank in the "duplicate" column

if they enter in more than once outside of a 30 minute window (leave, wait at least 30 min, then re-enter) the duplicate column should be ">2"

if they enter only twice outside of a 30 minute window duplicate column should be "x"

if their entrances are within the 30 min window or they only come once then leave blank.

Below is the excel sample w/ data and the excel formula.

Excel formula also here: 

=IF(COUNTIFS($A$1:A6,A6,$B$1:B6,B6,$C$1:C6,C6,$E$1:E6,E6)>2,">2",IF(AND((ABS(SUMIFS(D$1:$D5,B$1:B5,B6,A$1:$A5,A6,E$1:$E5,E6,C$1:$C5,C6)-D6)*(60*24))<31,(COUNTIFS($A$1:A5,A6,$B$1:B5,B6)>0)),"x",""))

There is no need to recreate this formula, unless it is the only way to get it done.

Thanks so much this will be a huge help!

IB (Data Sample).xlsx

AshRez

Hi Nick,

I was trying to understand your needs but your samples throw me off

1- Records for "AE Karioty" it shows x. but based on your requirements statement, "if their entrances are within the 30 min window or they only come once then leave blank",, which is 4 minutes only, so why there is x on records #2,?

2- Records for "J GP", re-entry 17:36 <52 Minutes> 18:28 <23 Minutes> 18:51 was 75 minutes later, why is the 'duplicate' value ">2" at one and not all, as you stated "if they enter in more than once outside of a 30 minute window (leave, wait at least 30 min, then re-enter) the duplicate column should be ">2""

3- Why the records for "AL D" showing Registration Date Apr 1, while Registration Date Local is Mar 31?

4- what is the difference between Registration Date, and Registration Date Local?

5- is this calculated based in the same day, or if they registered again in other days?

so you see, your statements are confusing and the sample made it even more confusing.

Maybe you can shed more clarity if you still need help with that.