If Statement (maybe) that takes into consideration several factors

[FanofExcel18]

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]

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

 

[michaeltsmith93]

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]

Just another way

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	FullName	Online/Offline	Day			Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	17/11/2019 01:03:00 a.m.	John Smith	Online	Sun		John Smith	4 hrs 28 mins	0 hrs 0 mins	0 hrs 0 mins	0 hrs 0 mins	0 hrs 0 mins
3	17/11/2019 05:31:00 a.m.	John Smith	Offline	Sun		Jane Doe	5 hrs 40 mins					0 hrs 0 mins	0 hrs 0 mins
4	17/11/2019 05:31:00 a.m.	John Smith	Online	Sun
5	17/11/2019 05:31:00 a.m.	John Smith	Offline	Sun
6	17/11/2019 07:50:00 a.m.	Jane Doe	Online	Sun
7	17/11/2019 12:31:00 p.m.	Jane Doe	Offline	Sun
8	17/11/2019 01:30:00 p.m.	Jane Doe	Online	Sun
9	17/11/2019 01:03:00 a.m.	John Smith	Online	Mon
10	17/11/2019 05:31:00 a.m.	John Smith	Offline	Tue
11	17/11/2019 05:31:00 a.m.	John Smith	Online	Wed
12	17/11/2019 05:36:00 a.m.	John Smith	Offline	Thu
13	17/11/2019 07:50:00 a.m.	Jane Doe	Online	Fri
14	17/11/2019 12:31:00 p.m.	Jane Doe	Offline	Sat
15	17/11/2019 01:30:00 p.m.	Jane Doe	Online	Sun
Sheet

Cell Formulas
Range	Formula
G2:M3	G2	=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]

Did you get it working?