get the earliest login time among dates

uwmarkyo · Jan 24, 2023

I have a sample like this: dataset

I tried to get the earliest login time for certain date and certain agent:

I am not able to get the result I want. can someone give me a guidance?
dataset
Thanks,

awoohaw · Jan 24, 2023

Im not sure if you wanted the min by date or agent, so this has both.
Does this work for you?

Mr Excel Questions.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

2

Agent_Nm

Date

login time

logout time

3

Apple

1/4/2023

8:00:09

16:30:18

Name

Earliest Login by Name

Date

Earliest Login by Date

4

Apple

1/5/2023

8:01:29

16:31:08

Apple

8:00:09 AM

1/3/2023

7:55:00 AM

5

Apple

1/6/2023

8:12:22

16:31:32

Apricot

8:00:27 AM

1/4/2023

7:54:37 AM

6

Apricot

1/3/2023

8:00:43

16:31:30

Asian Pear

8:00:36 AM

1/5/2023

12:48:22 AM

7

Apricot

1/4/2023

8:19:08

16:31:02

Avocado

8:00:20 AM

1/6/2023

7:57:23 AM

8

Apricot

1/5/2023

8:00:27

16:31:45

Banana

8:58:02 AM

1/8/2023

3:13:52 AM

9

Avocado

1/4/2023

8:00:43

16:30:10

Bell fruit

8:00:07 AM

10

Avocado

1/6/2023

8:00:20

16:30:04

Blueberry

8:00:27 AM

11

Banana

1/3/2023

8:58:02

17:34:21

Breadfruit

8:51:38 AM

12

Banana

1/4/2023

8:58:46

17:37:37

Cherry

7:59:26 AM

13

Banana

1/5/2023

9:00:09

17:31:13

Chinese Bayberry

3:13:52 AM

14

Banana

1/6/2023

8:58:27

17:31:41

Coconut

7:59:34 AM

15

Blueberry

1/3/2023

8:30:40

8:32:25

Dragon Fruit

8:30:32 AM

16

Blueberry

1/3/2023

8:32:27

10:03:41

Durian

10:30:43 AM

17

Blueberry

1/3/2023

10:03:42

10:40:37

Fig

10:30:07 AM

18

Blueberry

1/3/2023

10:40:38

14:05:21

Grape

7:59:04 AM

19

Blueberry

1/3/2023

14:05:22

17:00:15

Grapefruit

7:58:28 AM

20

Blueberry

1/4/2023

8:29:25

10:34:52

Jackfruit

12:48:22 AM

21

Blueberry

1/4/2023

11:09:44

16:33:10

Japanese Persimmon

9:00:34 AM

Sheet7

Cell Formulas
Range	Formula
F4:G51	F4	=LET( nm,AgentTime[Agent_Nm], lcol,SORT(UNIQUE(AgentTime[Agent_Nm])), ss,AgentTime[login time], mt,MINIFS(ss,nm,lcol), HSTACK(lcol,mt))
I4:J8	I4	=LET( dt,AgentTime[Date], lcol,SORT(UNIQUE(dt)), ss,AgentTime[login time], mt,MINIFS(ss,dt,lcol), HSTACK(lcol,mt))
Dynamic array formulas.

alansidman · Jan 24, 2023

With Power Query earliest Login by Name

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"login time", type time}, {"logout time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Agent_Nm"}, {{"Earliest Login", each List.Min([login time]), type nullable time}})
in
    #"Grouped Rows"

Earliest login by date

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"login time", type time}, {"logout time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Earliest Login", each List.Min([login time]), type nullable time}})
in
    #"Grouped Rows"

uwmarkyo · Jan 24, 2023

awoohaw said:
Im not sure if you wanted the min by date or agent, so this has both.
Does this work for you?

Mr Excel Questions.xlsx
A B C D E F G H I J K
1
2 Agent_Nm Date login time logout time
3 Apple 1/4/2023 8:00:09 16:30:18 Name Earliest Login by Name Date Earliest Login by Date
4 Apple 1/5/2023 8:01:29 16:31:08 Apple 8:00:09 AM 1/3/2023 7:55:00 AM
5 Apple 1/6/2023 8:12:22 16:31:32 Apricot 8:00:27 AM 1/4/2023 7:54:37 AM
6 Apricot 1/3/2023 8:00:43 16:31:30 Asian Pear 8:00:36 AM 1/5/2023 12:48:22 AM
7 Apricot 1/4/2023 8:19:08 16:31:02 Avocado 8:00:20 AM 1/6/2023 7:57:23 AM
8 Apricot 1/5/2023 8:00:27 16:31:45 Banana 8:58:02 AM 1/8/2023 3:13:52 AM
9 Avocado 1/4/2023 8:00:43 16:30:10 Bell fruit 8:00:07 AM
10 Avocado 1/6/2023 8:00:20 16:30:04 Blueberry 8:00:27 AM
11 Banana 1/3/2023 8:58:02 17:34:21 Breadfruit 8:51:38 AM
12 Banana 1/4/2023 8:58:46 17:37:37 Cherry 7:59:26 AM
13 Banana 1/5/2023 9:00:09 17:31:13 Chinese Bayberry 3:13:52 AM
14 Banana 1/6/2023 8:58:27 17:31:41 Coconut 7:59:34 AM
15 Blueberry 1/3/2023 8:30:40 8:32:25 Dragon Fruit 8:30:32 AM
16 Blueberry 1/3/2023 8:32:27 10:03:41 Durian 10:30:43 AM
17 Blueberry 1/3/2023 10:03:42 10:40:37 Fig 10:30:07 AM
18 Blueberry 1/3/2023 10:40:38 14:05:21 Grape 7:59:04 AM
19 Blueberry 1/3/2023 14:05:22 17:00:15 Grapefruit 7:58:28 AM
20 Blueberry 1/4/2023 8:29:25 10:34:52 Jackfruit 12:48:22 AM
21 Blueberry 1/4/2023 11:09:44 16:33:10 Japanese Persimmon 9:00:34 AM
Sheet7
Cell Formulas
Range Formula
F4:G51 F4 =LET( nm,AgentTime[Agent_Nm], lcol,SORT(UNIQUE(AgentTime[Agent_Nm])), ss,AgentTime[login time], mt,MINIFS(ss,nm,lcol), HSTACK(lcol,mt))
I4:J8 I4 =LET( dt,AgentTime[Date], lcol,SORT(UNIQUE(dt)), ss,AgentTime[login time], mt,MINIFS(ss,dt,lcol), HSTACK(lcol,mt))
Dynamic array formulas.

Thanks for your quick respond.
I tried this method. I didn't get it right.
I think it's because I don't know what dynamic array formula is.

in my dataset, I have another tab is for result. the result formula should look up agent name, date, and find the earliest login time.

uwmarkyo · Jan 24, 2023

alansidman said:

With Power Query earliest Login by Name

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"login time", type time}, {"logout time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Agent_Nm"}, {{"Earliest Login", each List.Min([login time]), type nullable time}})
in
    #"Grouped Rows"

Earliest login by date

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"login time", type time}, {"logout time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Earliest Login", each List.Min([login time]), type nullable time}})
in
    #"Grouped Rows"

This method also works ideally. I never thought of using power query. Great inspiration, thanks.
However, I would like to learn a formula method in order to gain more knowledge.

awoohaw · Jan 24, 2023

Here is a solution with more basic formulas.

Mr Excel Questions.xlsx

A

B

C

D

E

F

G

H

1

2

Agent_Nm

Date

login time

logout time

3

Apple

1/4/2023

8:00:09

16:30:18

Name

Time of Earliest Log In

Date of Earliest Login

4

Apple

1/5/2023

8:01:29

16:31:08

Apple

8:00:09 AM

1/4/2023

5

Apple

1/6/2023

8:12:22

16:31:32

Apricot

8:00:27 AM

1/6/2023

6

Apricot

1/3/2023

8:00:43

16:31:30

Asian Pear

8:00:36 AM

1/5/2023

7

Apricot

1/4/2023

8:19:08

16:31:02

Avocado

8:00:20 AM

1/6/2023

8

Apricot

1/5/2023

8:00:27

16:31:45

Banana

8:58:02 AM

1/3/2023

9

Asian Pear

1/3/2023

8:01:56

16:30:40

Bell fruit

8:00:07 AM

1/3/2023

10

Asian Pear

1/4/2023

8:01:24

16:35:41

Blueberry

8:00:27 AM

1/6/2023

11

Asian Pear

1/5/2023

8:01:51

16:31:24

Breadfruit

8:51:38 AM

1/6/2023

12

Asian Pear

1/6/2023

8:00:36

16:30:26

Cherry

7:59:26 AM

1/5/2023

13

Avocado

1/4/2023

8:00:43

16:30:10

Chinese Bayberry

3:13:52 AM

1/6/2023

14

Avocado

1/6/2023

8:00:20

16:30:04

Coconut

7:59:34 AM

1/8/2023

15

Banana

1/3/2023

8:58:02

17:34:21

Dragon Fruit

8:30:32 AM

1/6/2023

16

Banana

1/4/2023

8:58:46

17:37:37

Durian

10:30:43 AM

1/3/2023

Get Min Time Agent 2

Cell Formulas
Range	Formula
F4:F51	F4	=SORT(UNIQUE(AgentTime2[Agent_Nm]))
G4:G16	G4	=MINIFS(AgentTime2[login time],AgentTime2[Agent_Nm],F4)
H4:H16	H4	=INDEX(AgentTime2[Date],MATCH(F4&G4,AgentTime2[Agent_Nm]&AgentTime2[login time]))
Dynamic array formulas.

uwmarkyo · Jan 25, 2023

awoohaw said:
Here is a solution with more basic formulas.

Mr Excel Questions.xlsx
A B C D E F G H
1
2 Agent_Nm Date login time logout time
3 Apple 1/4/2023 8:00:09 16:30:18 Name Time of Earliest Log In Date of Earliest Login
4 Apple 1/5/2023 8:01:29 16:31:08 Apple 8:00:09 AM 1/4/2023
5 Apple 1/6/2023 8:12:22 16:31:32 Apricot 8:00:27 AM 1/6/2023
6 Apricot 1/3/2023 8:00:43 16:31:30 Asian Pear 8:00:36 AM 1/5/2023
7 Apricot 1/4/2023 8:19:08 16:31:02 Avocado 8:00:20 AM 1/6/2023
8 Apricot 1/5/2023 8:00:27 16:31:45 Banana 8:58:02 AM 1/3/2023
9 Asian Pear 1/3/2023 8:01:56 16:30:40 Bell fruit 8:00:07 AM 1/3/2023
10 Asian Pear 1/4/2023 8:01:24 16:35:41 Blueberry 8:00:27 AM 1/6/2023
11 Asian Pear 1/5/2023 8:01:51 16:31:24 Breadfruit 8:51:38 AM 1/6/2023
12 Asian Pear 1/6/2023 8:00:36 16:30:26 Cherry 7:59:26 AM 1/5/2023
13 Avocado 1/4/2023 8:00:43 16:30:10 Chinese Bayberry 3:13:52 AM 1/6/2023
14 Avocado 1/6/2023 8:00:20 16:30:04 Coconut 7:59:34 AM 1/8/2023
15 Banana 1/3/2023 8:58:02 17:34:21 Dragon Fruit 8:30:32 AM 1/6/2023
16 Banana 1/4/2023 8:58:46 17:37:37 Durian 10:30:43 AM 1/3/2023
Get Min Time Agent 2
Cell Formulas
Range Formula
F4:F51 F4 =SORT(UNIQUE(AgentTime2[Agent_Nm]))
G4:G16 G4 =MINIFS(AgentTime2[login time],AgentTime2[Agent_Nm],F4)
H4:H16 H4 =INDEX(AgentTime2[Date],MATCH(F4&G4,AgentTime2[Agent_Nm]&AgentTime2[login time]))
Dynamic array formulas.

Thanks for your help.
I just notice one thing, this blue table is exactly what I need. Can you show me how you formula this? Thanks

awoohaw · Jan 25, 2023

@uwmarkyo , the blue table in my answer is just your agent data table moved onto the same spreadsheet i was doing my work on. I guess I did not understand your question correctly.

What is the significance of the last picture image? What question do you have about that? The two columns in my solution are independent of each other, based upon my interpretation of your question. If you want the DATE that the AGENT logged on at the earliest time, it is another calculation.

uwmarkyo · Jan 25, 2023

awoohaw said:
@uwmarkyo , the blue table in my answer is just your agent data table moved onto the same spreadsheet i was doing my work on. I guess I did not understand your question correctly.

What is the significance of the last picture image? What question do you have about that? The two columns in my solution are independent of each other, based upon my interpretation of your question. If you want the DATE that the AGENT logged on at the earliest time, it is another calculation.

Sorry that I didn't explain clearly.
As you can see this is part of my dataset,

Blueberry has multiple logins and logouts on the same date.
Ideally, it will show every agent's earliest login time and the last logout time on each day as below,

thanks for your patience.

Fluff · Jan 25, 2023

How about

sample.xlsx

A

B

C

D

E

F

G

H

I

J

1

Agent_Nm

Date

login time

logout time

2

Apple

04/01/2023

08:00:09

16:30:18

Apple

04/01/2023

08:00:09

16:30:18

3

Apple

05/01/2023

08:01:29

16:31:08

Apple

05/01/2023

08:01:29

16:31:08

4

Apple

06/01/2023

08:12:22

16:31:32

Apple

06/01/2023

08:12:22

16:31:32

5

Apricot

03/01/2023

08:00:43

16:31:30

Apricot

03/01/2023

08:00:43

16:31:30

6

Apricot

04/01/2023

08:19:08

16:31:02

Apricot

04/01/2023

08:19:08

16:31:02

7

Apricot

05/01/2023

08:00:27

16:31:45

Apricot

05/01/2023

08:00:27

16:31:45

8

Avocado

04/01/2023

08:00:43

16:30:10

Avocado

04/01/2023

08:00:43

16:30:10

9

Avocado

06/01/2023

08:00:20

16:30:04

Avocado

06/01/2023

08:00:20

16:30:04

10

Banana

03/01/2023

08:58:02

17:34:21

Banana

03/01/2023

08:58:02

17:34:21

11

Banana

04/01/2023

08:58:46

17:37:37

Banana

04/01/2023

08:58:46

17:37:37

12

Banana

05/01/2023

09:00:09

17:31:13

Banana

05/01/2023

09:00:09

17:31:13

13

Banana

06/01/2023

08:58:27

17:31:41

Banana

06/01/2023

08:58:27

17:31:41

14

Blueberry

03/01/2023

08:30:40

08:32:25

Blueberry

03/01/2023

08:00:27

17:00:15

15

Blueberry

03/01/2023

08:32:27

10:03:41

Blueberry

04/01/2023

08:00:30

16:33:10

16

Blueberry

03/01/2023

10:03:42

10:40:37

Blueberry

05/01/2023

08:00:42

17:00:03

17

Blueberry

03/01/2023

10:40:38

14:05:21

Blueberry

06/01/2023

08:00:44

17:00:08

18

Blueberry

03/01/2023

14:05:22

17:00:15

Cherry

03/01/2023

07:59:26

16:35:40

19

Blueberry

04/01/2023

08:29:25

10:34:52

Cherry

04/01/2023

08:00:37

16:32:13

20

Blueberry

04/01/2023

11:09:44

16:33:10

Cherry

05/01/2023

07:59:26

16:29:27

21

Blueberry

05/01/2023

08:30:08

17:00:03

Cherry

06/01/2023

08:00:40

16:29:11

22

Blueberry

06/01/2023

08:30:26

10:02:39

Coconut

03/01/2023

08:05:38

16:33:53

23

Blueberry

06/01/2023

10:07:41

12:12:28

Coconut

04/01/2023

07:59:56

16:31:05

24

Blueberry

06/01/2023

12:12:30

16:57:34

Coconut

05/01/2023

08:00:20

16:31:22

25

Blueberry

06/01/2023

16:57:36

17:00:08

Coconut

06/01/2023

07:59:34

16:30:13

26

Cherry

03/01/2023

07:59:26

16:35:40

Grape

03/01/2023

07:59:30

16:30:03

27

Cherry

04/01/2023

08:00:37

16:32:13

Grape

04/01/2023

07:59:37

16:30:26

28

Cherry

05/01/2023

07:59:26

16:29:27

Grape

05/01/2023

07:59:16

16:30:01

29

Cherry

06/01/2023

08:00:40

16:29:11

Grape

06/01/2023

07:59:04

16:30:02

30

Coconut

03/01/2023

08:05:38

16:33:53

Grapefruit

03/01/2023

08:04:04

16:31:56

Agent_log

Cell Formulas
Range	Formula
G2:H182	G2	=UNIQUE(AgentTime[[Agent_Nm]:[Date]])
I2:I182	I2	=MINIFS(C:C,A:A,INDEX(G2#,,1),B:B,INDEX(G2#,,2))
J2:J182	J2	=MAXIFS(D:D,A:A,INDEX(G2#,,1),B:B,INDEX(G2#,,2))
Dynamic array formulas.

But you will need to change the login & logout "times2 to actual time, rather than text.

get the earliest login time among dates

uwmarkyo

New Member

Excel Facts

awoohaw

Well-known Member

alansidman

Well-known Member

uwmarkyo

New Member

uwmarkyo

New Member

awoohaw

Well-known Member

uwmarkyo

New Member

Attachments

awoohaw

Well-known Member

uwmarkyo

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

get the earliest login time among dates

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Attachments

Well-known Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock