MidnightRider
New Member
- Joined
- Jan 18, 2019
- Messages
- 6
got a formula that looks up a cell in another tab to bring in a time
=IFERROR(LOOKUP(2,1/(ALILO!$A$2:$A$15940=DataView!$C$1)/(ALILO!$B$2:$B$15940=DataView!B28),(ALILO!$E$2:$E$15940))," ")
ALILO is the tab the data is located and is in column E
basically this looks up the date first from the DataView tab (ALILO!$A$2:$A$15940=DataView!$C$1)
then looks up the users name from the same tab (ALILO!$B$2:$B$15940=DataView!B28)
then looks for the time the use logged in (ALILO!$E$2:$E$15940)
the problem is, sometimes a user may log off for a few minutes then log back in. when this happens, there are 2 entries on the ALILO tab for the same user like this
Date Name Extension Login Logout
1/17/2019 User_Name 3447228 8:05AM 9:54AM
1/17/2019 USer_Name 3447228 9:58AM 4:31PM
what happens with the formula is that it only pulls the second instance, which is fine for the logout time, but not the login time
i have the same formula in another cell on the DataView tab for the logout time, its the same except it looks in column G for the logout time. dont have any issues there as it grabs the second instance if there are 2, which works
the data in the ALILO tab is pulled in from a report. yes i could manually edit the data before i do the formula, but trying to avoid that, need to sohw the fact that people have logged out and back in
how can i get the formula to look up the first instance of the login time?
=IFERROR(LOOKUP(2,1/(ALILO!$A$2:$A$15940=DataView!$C$1)/(ALILO!$B$2:$B$15940=DataView!B28),(ALILO!$E$2:$E$15940))," ")
ALILO is the tab the data is located and is in column E
basically this looks up the date first from the DataView tab (ALILO!$A$2:$A$15940=DataView!$C$1)
then looks up the users name from the same tab (ALILO!$B$2:$B$15940=DataView!B28)
then looks for the time the use logged in (ALILO!$E$2:$E$15940)
the problem is, sometimes a user may log off for a few minutes then log back in. when this happens, there are 2 entries on the ALILO tab for the same user like this
Date Name Extension Login Logout
1/17/2019 User_Name 3447228 8:05AM 9:54AM
1/17/2019 USer_Name 3447228 9:58AM 4:31PM
what happens with the formula is that it only pulls the second instance, which is fine for the logout time, but not the login time
i have the same formula in another cell on the DataView tab for the logout time, its the same except it looks in column G for the logout time. dont have any issues there as it grabs the second instance if there are 2, which works
the data in the ALILO tab is pulled in from a report. yes i could manually edit the data before i do the formula, but trying to avoid that, need to sohw the fact that people have logged out and back in
how can i get the formula to look up the first instance of the login time?
