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

Hi Davy,

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

I've added formulas to columns E & F that will show a 1 if the first relevant re-login timestamp in column I falls within 24 hours of initial 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.

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

Thanks so much Ron. Your approach is brilliant!