If Statement (maybe) that takes into consideration several factors

FanofExcel18

Board Regular
Joined
Jun 7, 2018
Messages
65
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:
DateFull NameOnline/OfflineDay
11/17/2019 1:03​
John SmithOnlineSun
11/17/2019 5:31​
John SmithOfflineSun
11/17/2019 5:31​
John SmithOnlineSun
11/17/2019 5:31​
John SmithOfflineSun
11/17/2019 7:50​
Jane DoeOnlineSun
11/17/2019 12:31​
Jane DoeOfflineSun
11/17/2019 13:30​
Jane DoeOnlineSun
11/17/2019 1:03​
John SmithOnlineMon
11/17/2019 5:31​
John SmithOfflineTue
11/17/2019 5:31​
John SmithOnlineWed
11/17/2019 5:31​
John SmithOfflineThu
11/17/2019 7:50​
Jane DoeOnlineFri
11/17/2019 12:31​
Jane DoeOfflineSat
11/17/2019 13:30​
Jane DoeOnlineSun

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:
SunMonTueWedThuFriSat
John Smith4 hour 28 mins
Jane Doe
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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 """)
 
Upvote 0
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 """),"")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top