# If Statement (maybe) that takes into consideration several factors

#### FanofExcel18

##### New Member
Issue: Need to calculate the times per person, per day using minimum/maximum (or that's how i figured it out). Each day, there could be multiple timestamps. Need to figure out the oldest and newest, then calculate the time in between that based on person and day.

Appreciate any help

Example data:
 Date Full Name Online/Offline Day 11/17/2019 1:03​ John Smith Online Sun 11/17/2019 5:31​ John Smith Offline Sun 11/17/2019 5:31​ John Smith Online Sun 11/17/2019 5:31​ John Smith Offline Sun 11/17/2019 7:50​ Jane Doe Online Sun 11/17/2019 12:31​ Jane Doe Offline Sun 11/17/2019 13:30​ Jane Doe Online Sun 11/17/2019 1:03​ John Smith Online Mon 11/17/2019 5:31​ John Smith Offline Tue 11/17/2019 5:31​ John Smith Online Wed 11/17/2019 5:31​ John Smith Offline Thu 11/17/2019 7:50​ Jane Doe Online Fri 11/17/2019 12:31​ Jane Doe Offline Sat 11/17/2019 13:30​ Jane Doe Online Sun

Formula that I came up with: =TEXT((MAX(A2:A5)-MIN(A2:A5)),"h"" hrs ""m"" mins """) ..This comes out to be 4 hr 28 mins... But I need to get it to the point where results will fill in this way:
 Sun Mon Tue Wed Thu Fri Sat John Smith 4 hour 28 mins Jane Doe

#### FanofExcel18

##### New Member
Still having an issue figuring out, appreciate any thought on this.

#### michaeltsmith93

##### Board Regular
Assuming that both of these tables is referenced to cell A1, then this will work. Sheet3 is the location of the example data table.

`=TEXT(MAXIFS(Sheet3!\$A:\$A,Sheet3!\$B:\$B,\$A2,Sheet3!\$D:\$D,B\$1)-MINIFS(Sheet3!\$A:\$A,Sheet3!\$B:\$B,\$A2,Sheet3!\$D:\$D,B\$1),"h"" hrs ""m"" mins """)`

#### DanteAmor

##### Well-known Member
Just another way

xl2bb.xlam
ABCDEFGHIJKLM
1DateFullNameOnline/OfflineDaySunMonTueWedThuFriSat
217/11/2019 01:03:00 a.m.John SmithOnlineSunJohn Smith
317/11/2019 05:31:00 a.m.John SmithOfflineSunJane Doe
417/11/2019 05:31:00 a.m.John SmithOnlineSun
517/11/2019 05:31:00 a.m.John SmithOfflineSun
617/11/2019 07:50:00 a.m.Jane DoeOnlineSun
717/11/2019 12:31:00 p.m.Jane DoeOfflineSun
817/11/2019 01:30:00 p.m.Jane DoeOnlineSun
917/11/2019 01:03:00 a.m.John SmithOnlineMon
1017/11/2019 05:31:00 a.m.John SmithOfflineTue
1117/11/2019 05:31:00 a.m.John SmithOnlineWed
1217/11/2019 05:36:00 a.m.John SmithOfflineThu
1317/11/2019 07:50:00 a.m.Jane DoeOnlineFri
1417/11/2019 12:31:00 p.m.Jane DoeOfflineSat
1517/11/2019 01:30:00 p.m.Jane DoeOnlineSun
 Sheet
Cell Formulas
Range(s)Formula
G2:M3G2=IFERROR(TEXT(LOOKUP(2,1/((\$B\$2:\$B\$15=\$F2)*(\$D\$2:\$D\$15=G\$1)),\$A\$2:\$A\$15)-INDEX(\$A\$2:\$A\$15,MATCH(\$F2&"|"&G\$1,INDEX(\$B\$2:\$B\$15&"|"&\$D\$2:\$D\$15,0),0)),"h"" hrs ""m"" mins """),"")

#### michaeltsmith93

##### Board Regular
Did you get it working?

1,081,767
Messages
5,361,164
Members
400,617
Latest member
barron1

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...