On Tue, 25 Sep 2012 02:13:48 +0000, spamdavy wrote:


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")