Google Sheets: how to match data and return needed value only..

[ricky82]

Hi all;
I'm trying to figure out how to obtain matching the country and dates as well login/logout time


- Need to match country to fetch first Start Timestamp & last End Timestamp
- to return value of hh:mm AM/PM only from the matching.


Was trying to use this below but cant seems to get the last logout time. However my earlier data has time only but now its combined date and time...


{=IFERROR(IF($G13="OD","OD",IF($G13="RD","RD",IF($G13="Leave","Leave",IF($G13="","",INDEX('Login Data'!$F:$F,MATCH(1,(TRIM('Login Data'!$A:$A)=TRIM($C13))*('Login Data'!$E:$E=$G$2),0)))))),"")}

[davsth]

entered as an array with shft ctrl enter

in C3
=MIN(IF('Login Data'!$B$2:$B$19=May!$A3,IF(INT('Login Data'!$E$2:$E$19)=May!B$1,'Login Data'!$E$2:$E$19,"")))

in D3
=MAX(IF('Login Data'!$B$2:$B$19=May!$A3,IF(INT('Login Data'!$F$2:$F$19)=May!B$1,'Login Data'!$F$2:$F$19,"")))

[ricky82]

Hi There;
Trying to figure out to use same array formula in google sheet. However its not returning the required value. Wont it work in google sheet?

[AliGW]

If it needs to work in Google Sheets you should gave said so right at the start, and you should have posted in the appropriate section. I am moving this thread there now.

[ricky82]

I'm sorry...cause this was working in excel..but when tried in google sheet its not..i will resolve this thread since it was addressed earlier..

[AliGW]

This thread has been moved - please continue here. I will remove the solved tag for now.

[ricky82]

Hi all;
I'm trying to figure out how to obtain matching the country and dates as well login/logout time in google sheet using array formula. I had it worked in Excel but not in google sheet. And some formats and return value is not performing as it should.

Hope anyone can guide

https://docs.google.com/spreadsheets...it?usp=sharing

Very much thanks in advance.

[ricky82]

Hi All;
I manage to resolve the issue..10q

[ricky82]

Hi All;
I manage to resolve the issue..10q

[AliGW]

Please share the solution for the benefit of all.