# 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

Book1
ABCDEFGHIJKLM
1DateFullNameOnline/OfflineDaySunMonTueWedThuFriSat
217/11/2019 01:03:00 a.m.John SmithOnlineSunJohn Smith4 hrs 28 mins 0 hrs 0 mins 0 hrs 0 mins 0 hrs 0 mins 0 hrs 0 mins
317/11/2019 05:31:00 a.m.John SmithOfflineSunJane Doe5 hrs 40 mins 0 hrs 0 mins 0 hrs 0 mins
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
RangeFormula
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?

Threads
1,085,372
Messages
5,383,262
Members
401,820
Latest member
RustEE2020