Sumifs to find minimum ad maximum.

[Akbarov]

Hey Dear community,

I have a login details which i need to report. i can't figure out how can i ask excel to find first login and last log out data for each day of month for each person from table? Can anyone help me with VBA or formula please?

B - DATE
C - Name surname
F - Client id
J - login / log out time

 

[Aryatect]

Hi will below solve your purpose?:

Book1
	B	C	F	K	M	N	O	P
1	Date	Name	ID	Login/Logout	Person	Date	Login	Logout
2	8/5/2019	A	A	5:52 PM	A	8/5/2019	5:52:02 PM	8:04:23 PM
3	8/6/2019	A	A	6:17 AM	A	8/6/2019	6:17:10 AM	6:03:38 PM
4	8/7/2019	A	A	4:05 PM	A	8/7/2019	4:05:27 PM	11:00:23 PM
5	8/8/2019	A	A	5:09 PM	A	8/8/2019	5:09:10 PM	9:05:41 PM
6	8/9/2019	A	A	2:48 AM	A	8/9/2019	12:26:35 AM	2:48:42 AM
7	8/10/2019	A	A	7:16 AM	A	8/10/2019	7:16:37 AM	12:57:57 PM
8	8/11/2019	A	A	3:25 AM	A	8/11/2019	3:25:55 AM	7:34:06 AM
9	8/12/2019	A	A	3:27 AM	A	8/12/2019	3:27:37 AM	4:12:54 PM
10	8/13/2019	A	A	2:33 PM	A	8/13/2019	2:06:49 AM	2:33:35 PM
11	8/14/2019	A	A	2:25 PM	A	8/14/2019	2:21:48 PM	2:25:10 PM
12	8/15/2019	A	A	1:59 AM	A	8/15/2019	1:59:15 AM	9:22:26 PM
13	8/16/2019	A	A	10:24 PM	A	8/16/2019	12:15:40 PM	10:24:18 PM
14	8/6/2019	A	A	6:46 AM	A	8/19/2019	12:53:42 AM	12:03:03 PM
15	8/6/2019	A	A	6:03 PM	A	8/20/2019	1:22:24 AM	1:59:59 AM
16	8/19/2019	A	A	12:53 AM	A	8/21/2019	3:44:37 PM	5:20:19 PM
17	8/20/2019	A	A	1:22 AM	A	8/22/2019	9:15:31 AM	1:08:14 PM
18	8/21/2019	A	A	3:44 PM	A	8/23/2019	6:00:26 PM	8:18:51 PM
19	8/22/2019	A	A	9:15 AM
20	8/23/2019	A	A	6:00 PM
21	8/5/2019	A	A	8:04 PM
22	8/6/2019	A	A	11:42 AM
23	8/7/2019	A	A	11:00 PM
24	8/8/2019	A	A	9:05 PM
25	8/9/2019	A	A	12:26 AM
26	8/10/2019	A	A	12:57 PM
27	8/11/2019	B	B	6:55 PM
28	8/12/2019	B	B	4:52 AM
29	8/13/2019	B	B	4:03 AM
30	8/14/2019	B	B	11:55 AM
31	8/15/2019	B	B	10:05 AM
32	8/16/2019	B	B	4:01 PM
33	8/17/2019	B	B	1:18 PM
34	8/18/2019	B	B	12:06 PM
35	8/19/2019	B	B	1:34 PM
36	8/20/2019	B	B	2:33 PM
37	8/21/2019	B	B	4:56 AM
38	8/22/2019	B	B	2:37 AM
39	8/23/2019	B	B	11:11 AM
40	8/11/2019	A	A	7:34 AM
41	8/12/2019	A	A	4:12 PM
42	8/13/2019	A	A	2:06 AM
43	8/14/2019	A	A	2:21 PM
44	8/15/2019	A	A	9:22 PM
45	8/16/2019	A	A	12:15 PM
46	8/19/2019	A	A	12:03 PM
47	8/20/2019	A	A	1:59 AM
48	8/21/2019	A	A	5:20 PM
49	8/22/2019	A	A	1:08 PM
50	8/23/2019	A	A	8:18 PM
Sheet1

Cell Formulas
Range	Formula
O2	=MINIFS($K$2:$K$50,$B$2:$B$50,N2,$C$2:$C$50,M2)
P2	=MAXIFS($K$2:$K$50,$B$2:$B$50,N2,$C$2:$C$50,M2)

 

[Akbarov]

Thank you for reply mr. but for some reason i get 0 result, O2 is time, when i change it to number it returns value, but it gives 0 as time

 

[Aryatect]

Zero comes when there is no entry on that particular day. Are you getting zero for all the examples above?

 

[Akbarov]

yes all examples, btw there is no blank cell

 

[Aryatect]

Can you please check if dates in Column B and time in column K are stored in the proper excel date and time format. As in 08/05/2019 should be 43682 if you convert to general number formatting and time 5:52 PM should be 0.744466307147698. Same should apply to column N for Date.

 

[Akbarov]

Dates are 43662 but problem with time when i change format it is not changing ( this problem happens when you take data from server )

tryed with VBA

Sub Convert()With Worksheets("DATA").Columns(1)
    .NumberFormat = "0"
    .Value = .Value
End With
End Sub

now it works , thanks for help

 

[Aryatect]

Glad could help and thanks for the feedback!