If Statement (maybe) that takes into consideration several factors

FanofExcel18

New Member
Joined
Jun 7, 2018
Messages
48
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
 

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
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
Joined
Dec 3, 2018
Messages
9,522
Office Version
2007
Platform
Windows
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 """),"")
 

Forum statistics

Threads
1,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top