What am I doing wrong?

[preethamdevraj]

I am trying retrieve the first ready time or logged in time if ready is not available. But unable to see it.. created this formula to get it, tell me what I am doing wrong?

=IF(AND(Data[Agent State]="Ready",Data[Agent Name]=D3),MINIFS(Data[State Transition Time],Data[Agent State],"Logged-in",Data[Agent Name],D3),MINIFS(Data[State Transition Time],Data[Agent State],"Ready",Data[Agent Name],D3)

Table name = Data, D3 has the value to refer to,.

Appreciate any help!!!

 

[awoohaw]

What is "State Transition TIme"? Is there a data element for Log In Time? A sample of your matrix would be useful? And the xl2bb add-in makes getting help from others easier.

 

[awoohaw]

Does this give you what you need:

Book2
	A	B	C	D	E	F	G
1
2	AgentName	AgentState	StateTransitionTime		AgentName	State	Time
3	John	Ready	6:35:00 AM		John	Ready	6:35:00 AM
4	Greg	Ready	11:13:00 AM		Greg	Ready	11:13:00 AM
5	Harry	Ready	3:58:00 PM		Harry	Ready	3:58:00 PM
6	Sally	Logged In	4:08:00 AM		Sally	Logged In	4:08:00 AM
7	Tina	Logged In	11:50:00 AM		Tina	Logged In	11:50:00 AM
8	Pam	Logged Out	10:31:00 PM
9	Jerry	Logged Out	11:32:00 PM
10
Sheet1

Cell Formulas
Range	Formula
E3:G7	E3	=FILTER(dTable,(dTable[AgentState]="Ready")+(dTable[AgentState]="Logged In"),"")
Dynamic array formulas.

 

[Alex Blakenburg]

Give this a try:

=MIN(
            FILTER(Data[Data'[State Transition Time],
                        (Data[Agent Name]=D3)*(Data[Agent State]="Ready"),
                         FILTER(Data[Data'[State Transition Time],
                                     (Data[Agent Name]=D3)*(Data[Agent State]="Logged-in"),
                                      "")
                         )
            )

 

[preethamdevraj]

What is "State Transition TIme"? Is there a data element for Log In Time? A sample of your matrix would be useful? And the xl2bb add-in makes getting help from others easier.

State Transition time contains both date and time of the activity performed.

 

[Alex Blakenburg]

Did either of the formulas work for you ?
If not please provide details and some sample data.

 

[awoohaw]

Did either of the formulas work for you ?
If not please provide details and some sample data.

@Alex Blakenburg , @preethamdevraj liked both of the formulas, i'm guessing they were satisfied.

 

[preethamdevraj]

Does this give you what you need:

Book2
	A	B	C	D	E	F	G
1
2	AgentName	AgentState	StateTransitionTime		AgentName	State	Time
3	John	Ready	6:35:00 AM		John	Ready	6:35:00 AM
4	Greg	Ready	11:13:00 AM		Greg	Ready	11:13:00 AM
5	Harry	Ready	3:58:00 PM		Harry	Ready	3:58:00 PM
6	Sally	Logged In	4:08:00 AM		Sally	Logged In	4:08:00 AM
7	Tina	Logged In	11:50:00 AM		Tina	Logged In	11:50:00 AM
8	Pam	Logged Out	10:31:00 PM
9	Jerry	Logged Out	11:32:00 PM
10
Sheet1

Cell Formulas
Range	Formula
E3:G7	E3	=FILTER(dTable,(dTable[AgentState]="Ready")+(dTable[AgentState]="Logged In"),"")
Dynamic array formulas.

Did either of the formulas work for you ?
If not please provide details and some sample data.

Here is my sample data...

Notice Agent 1 has has multiple "Logged-in" instances and "Ready", However for Agent 2 you only see Logged-in instances but not "Ready".
I want the cell to populate the latest Ready date/time, if there is no "Ready" then it should give me the Latest "Logged-in" date/time.

 

[awoohaw]

Okay, that is information that was not made clear. So you want the Maximum Time of Each Day of the Ready Time, if not the Maximum Log in Time ... what if the Last Ready Time is Before the Last Login Time? Do you want the login time or the ready time? Or would you want the Maximum of the two times? and what ever status is associated with that time? Can you be logged in multiple times of the day, like the ready time?

 

[Alex Blakenburg]

I am not at a computer at the moment, if you change MIN to MAX in my post #4, does that get you to what you need ?