Possible to determine whether any one instance within many satisfy a criteria?

[Guest]

Hello there,

I am stuck trying to figure out whether a list of users have logged i
within a specific 24 hour timeframe defined relative to their initia
login time.

In the attached spreadsheet:

A-first login - user ID
B-login timestamp - the day and time when that user first logged in
C-24 hr period - 24 hours after the first login
D-48 hr period - 48 hours after the first login

The idea is to figure out whether the users in Column H have re-logge
in between 24 and 48 hours after their first login. The same user coul
have logged in multiple times but I only care if that person has a
least one login instance that satisfy the criteria.

I greatly appreciate any insight into which Excel functions might hel
me solve this riddle.

Thank you in advance!
dav

+-------------------------------------------------------------------
|Filename: help with re-login timeframes.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=597
+-------------------------------------------------------------------

 

[Spencer101]

Hello there,

I am stuck trying to figure out whether a list of users have logged i
within a specific 24 hour timeframe defined relative to their initia
login time.

In the attached spreadsheet:

A-first login - user ID
B-login timestamp - the day and time when that user first logged in
C-24 hr period - 24 hours after the first login
D-48 hr period - 48 hours after the first login

The idea is to figure out whether the users in Column H have re-logge
in between 24 and 48 hours after their first login. The same user coul
have logged in multiple times but I only care if that person has a
least one login instance that satisfy the criteria.

I greatly appreciate any insight into which Excel functions might hel
me solve this riddle.

Thank you in advance!
davy

Hi Davy,

I'm not sure I've fully understood your requirement, but I've had a g
at what I -think- you mean. Hopefully it will give you a starting poin
at least.

I've added formulas to columns E & F that will show a 1 if the firs
relevant re-login timestamp in column I falls within 24 hours of initia
login (column E) or 48 hours of initial login (column F).

There is only one matching login ID so you only get one result.

Is this anywhere near what you were after?

S

+-------------------------------------------------------------------
|Filename: help with re-login timeframes.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=598
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

Hello there,

I am stuck trying to figure out whether a list of users have logged in
within a specific 24 hour timeframe defined relative to their initial
login time.

In the attached spreadsheet:

A-first login - user ID
B-login timestamp - the day and time when that user first logged in
C-24 hr period - 24 hours after the first login
D-48 hr period - 48 hours after the first login

The idea is to figure out whether the users in Column H have re-logged
in between 24 and 48 hours after their first login. The same user could
have logged in multiple times but I only care if that person has at
least one login instance that satisfy the criteria.

I greatly appreciate any insight into which Excel functions might help
me solve this riddle.

Thank you in advance!
davy


+-------------------------------------------------------------------+
|Filename: help with re-login timeframes.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=597|
+-------------------------------------------------------------------+

I would compare the re-login ID to the list of first logins. If that value is between 1 and 2, then the first login would have occurred 24-48 hours before.
This will be much easier than comparing to the first login, as you only have to look for one event. If you are checking against the first login, since there can be multiple second logins, you would have to check each second login, and this would be much more cumbersome to do in formulas (although simple with a macro or user defined function).

Formula for that time difference: =I2-VLOOKUP(H2,$A$2:$B$56,2,FALSE)

So something like:

=IFERROR(IF(AND((I2-VLOOKUP(H2,$A$2:$B$56,2,FALSE))>1,
(I2-VLOOKUP(H2,$A$2:$B$56,2,FALSE))<2),TRUE,FALSE),"No First login")

 

[Guest]

Thanks so much Ron. Your approach is brilliant

+-------------------------------------------------------------------
+-------------------------------------------------------------------