FILTER function looking at duration and time

dancer5

New Member
Joined
Jan 6, 2023
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm trying to modify a filter function I'm already using and have no idea where to start. Right now I have a very large data set and the filter function is looking for all occurrences >23hrs. What I'm trying to find now is all instances where a person has >23hrs over a 26hr time span. I have start dates and times and durations.

This is my current filter formula in A4 and resultant dataset. It works for finding >23hr:
duration temp.xlsx
ABCDEFG
123all sites
2First Blank25966
3IDTypeEpisode Date dd-mmm-yyyyEpisode time hh:mmhhmmss
4001-003A10-Sep-201914:484755621
5001-003A29-Apr-20219:02292935
6001-003A29-Apr-20219:02292935
7001-008A30-Dec-20195:291665321
8001-008A6-Jan-20204:39314739
9001-014A15-Oct-202014:4125138
10001-021A3-Apr-202111:33291035
11001-021A4-Apr-202116:441114058
12001-021A9-Apr-20218:30475459
13001-021A11-Apr-20218:251225827
14001-021A16-Apr-202111:24475545
15001-021A27-Apr-20210:04264827
16001-021A1-May-202116:46162358
17001-021A8-May-202110:51268352
18001-021A19-May-202115:27702325
19001-021A22-May-202113:50169287
20001-027A30-Apr-20216:57252419
21001-027A18-May-202122:1829127
22001-027A27-May-202123:10362223
23001-033A6-Dec-20214:513026
24001-033A13-Dec-202122:0644591
25001-033A26-Dec-20210:38361429
26001-033A2-Jan-20228:00302353
27001-033A6-Jan-202219:21261519
28001-033A10-Jan-20223:37311513
29001-033A13-Jan-20222:31285922
30001-033A19-Jan-20220:033636
31001-033A22-Jan-20229:39245421
32001-033A25-Jan-20229:1753462
33001-033A30-Jan-202216:2344028
34001-033A4-Feb-202219:11403825
35001-036A16-Jan-20228:184507
36001-036A18-Jan-20225:42244943
37001-036A26-Feb-20227:40992814
38002-006A6-Apr-20228:31421114
39002-006A8-Apr-20228:1126758
40002-006A15-May-202212:02423134
41002-006A17-May-20228:38694511
42002-006A20-May-20227:50264745
43002-006A25-May-20227:591052450
44002-006A30-May-20228:1846729
45002-006A2-Jun-20227:0272619
46002-006A5-Jun-202210:4244729
47002-006A17-Jun-20228:49245518
48004-001A19-Mar-20226:55255841
49007-007A17-Dec-20224:08251139
50009-005A28-Oct-20220:4459526
51009-005A16-Dec-20220:36315115
52009-005A17-Dec-202215:172643335
53011-011A26-Sep-202212:2535537
54012-001A11-Jun-20222:46384259
55012-004A3-Nov-202220:19292247
56012-004A13-Nov-202222:32421949
57015-001A10-May-202110:4224520
Sheet1
Cell Formulas
RangeFormula
C2C2=(MIN(IF('all sites'!B5:B29891="",ROW('all sites'!B5:B29891))))-1
A4:G57A4=FILTER('all sites'!A5:INDIRECT("'all sites'!G"&C2), IFERROR('all sites'!E5:INDIRECT("'all sites'!E"&C2),0)>A1, 0)
Dynamic array formulas.


And a sample of my data set:
IDTypeEpisode Date dd-mmm-yyyyEpisode time hh:mmhhmmss
001-002A14-Oct-20191:2817348
001-002A12-Oct-202023:400178
001-002A5-Nov-202013:240841
001-002A5-Nov-202023:35#VALUE!#VALUE!#VALUE!
001-002A14-Nov-202018:2915921
001-002A14-Nov-202018:2915921
001-002A30-Jan-20212:18#VALUE!#VALUE!#VALUE!
001-002A8-Feb-20219:260201
001-002A15-Feb-202111:3502343
001-002A27-Apr-202111:54#VALUE!#VALUE!#VALUE!
001-002A16-May-202119:100611
001-002A22-May-202122:29#VALUE!#VALUE!#VALUE!
001-002A24-May-202115:31#VALUE!#VALUE!#VALUE!
001-002A24-May-202115:39#VALUE!#VALUE!#VALUE!
001-002A24-May-202117:19069
001-002A24-May-202117:430933
001-002A28-May-202117:46#VALUE!#VALUE!#VALUE!
001-003A10-Sep-201914:484755621
001-003A7-Oct-20193:03#VALUE!#VALUE!#VALUE!
001-003A29-Oct-201910:1102024
001-003A18-Nov-201912:590110
001-003A26-Nov-201912:02#VALUE!#VALUE!#VALUE!
001-003A4-Dec-201912:0401033
001-003A4-Dec-201912:1601314
001-003A4-Dec-201913:400136
001-003A5-Dec-201912:500634
001-003A5-Dec-201912:5902817
001-003A5-Dec-201913:510463
001-003A7-Dec-201911:5403053
001-003A9-Dec-201921:05#VALUE!#VALUE!#VALUE!
001-003A16-Dec-201911:000146
001-003A16-Dec-201911:200616
001-003A17-Dec-201913:1901230
001-003A17-Dec-201914:1501414
001-003A17-Dec-201916:580954
001-003A18-Dec-201913:1005949
001-003A23-Dec-201911:0803740
001-003A24-Dec-201910:260157
001-003A27-Dec-201922:200911
001-003A29-Dec-201921:4702154
001-003A31-Dec-20199:0001046
001-003A1-Jan-202011:3802245
001-003A1-Jan-202017:0001343
001-003A1-Jan-202020:0702720
001-003A13-Sep-202015:5402717
001-003A13-Sep-202016:2214328
001-003A13-Sep-202018:2001111
001-003A13-Sep-202018:33062
001-003A13-Sep-202018:400940
001-003A13-Sep-202018:5101844
001-003A13-Sep-202019:1601336
001-003A13-Sep-202019:3101818
001-003A13-Sep-202019:5101242
001-003A13-Sep-202020:040473
001-003A13-Sep-202020:5502433
001-003A13-Sep-202021:2142127
001-003A14-Sep-20201:4823840
001-003A14-Sep-20204:303354
001-003A14-Sep-20208:124053
001-003A14-Sep-202012:14#VALUE!#VALUE!#VALUE!
001-003A25-Sep-20201:2202439
001-003A25-Sep-20203:240584
001-003A1-Oct-20207:1112934
001-003A1-Oct-20208:4302750
001-003A16-Dec-202015:1501525
001-003A16-Dec-202015:4601615
001-003A16-Dec-202016:121039
001-003A16-Dec-202017:2524023
001-003A16-Dec-202020:0703458
001-003A16-Dec-202020:5403828
001-003A16-Dec-202021:3904853
001-003A18-Dec-20205:300922
001-003A18-Dec-20205:4413243
001-003A18-Dec-20207:2204625
001-003A18-Dec-20208:1901932
001-003A18-Dec-20208:400844
001-003A18-Dec-20209:0724429
001-003A18-Dec-202011:552201
001-003A18-Dec-202014:1701147
001-003A18-Dec-202014:391221
001-003A18-Dec-202015:430641
001-003A18-Dec-202015:5002039
001-003A18-Dec-202016:1202428
001-003A18-Dec-202016:3802831
001-003A18-Dec-202017:09361
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Couldn't figure this one out, so I've created helper columns in my main dataset for combined date/time, +23hr from episode, -23hr from episode and then used SUMIFS to calculate the Total Time within 26 hrs for each entry. I was hoping to avoid this and just have a separate sheet that filters but this way will do the job.

duration temp.xlsx
ABCDEFGHIJK
4IDTypeEpisode Date dd-mmm-yyyyEpisode time hh:mmhhmmsscombined datetimeTotal within 26 hours26-26
5001-001A16-Aug-201915:2809198/16/19 15:282.28/17/19 17:288/15/19 13:28
6001-001A16-Aug-201915:5601088/16/19 15:562.28/17/19 17:568/15/19 13:56
7001-001A16-Aug-201916:08028488/16/19 16:082.28/17/19 18:088/15/19 14:08
8001-001A16-Aug-201916:4604488/16/19 16:462.28/17/19 18:468/15/19 14:46
9001-001A16-Aug-201918:38014408/16/19 18:382.28/17/19 20:388/15/19 16:38
10001-001A16-Aug-201920:00021538/16/19 20:002.28/17/19 22:008/15/19 18:00
11001-001A16-Aug-201921:4406268/16/19 21:442.28/17/19 23:448/15/19 19:44
12001-001A20-Aug-201913:2605148/20/19 13:265.98/21/19 15:268/19/19 11:26
13001-001A20-Aug-201914:34019318/20/19 14:345.98/21/19 16:348/19/19 12:34
14001-001A20-Aug-201915:1819258/20/19 15:185.98/21/19 17:188/19/19 13:18
15001-001A20-Aug-201916:39019478/20/19 16:396.08/21/19 18:398/19/19 14:39
16001-001A20-Aug-201917:40028118/20/19 17:406.48/21/19 19:408/19/19 15:40
17001-001A20-Aug-201918:1314378/20/19 18:137.08/21/19 20:138/19/19 16:13
18001-001A20-Aug-201920:20021438/20/19 20:207.18/21/19 22:208/19/19 18:20
19001-001A20-Aug-201921:490688/20/19 21:497.18/21/19 23:498/19/19 19:49
20001-001A20-Aug-201921:56019238/20/19 21:567.18/21/19 23:568/19/19 19:56
21001-001A20-Aug-201923:33011248/20/19 23:337.28/22/19 1:338/19/19 21:33
22001-001A20-Aug-201923:5009348/20/19 23:507.28/22/19 1:508/19/19 21:50
23001-001A21-Aug-201911:4101178/21/19 11:417.28/22/19 13:418/20/19 9:41
all sites
Cell Formulas
RangeFormula
C5:D23C5=N5
E5:E23E5=IF(X5=0, 0, V5)
F5:F23F5=IF(X5=0, V5, W5)
G5:G23G5=IF(X5=0, W5, X5)
H5:H23H5=[@[Episode Date dd-mmm-yyyy]]+[@[Episode time hh:mm]]
I5:I23I5=SUMIFS($E$5:$E$30000, $E$5:$E$30000, "<>#VALUE!", $A$5:$A$30000, A5, $H$5:$H$30000, "<="&J5, $H$5:$H$30000, ">="&K5)+((SUMIFS($F$5:$F$30000, $F$5:$F$30000, "<>#VALUE!", $A$5:$A$30000, A5, $H$5:$H$30000, "<="&J5, $H$5:$H$30000, ">="&K5))/60)
J5:J23J5=[@[combined datetime]]+(26/24)
K5:K23K5=[@[combined datetime]]-(26/24)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4Cell Value=YestextNO
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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