find the first which met the condition

[baobang212]

Hi everyone,

I have a file that contain chat history, so I would like to find the time of the first message that sent by real agent.
demo.PNG

Can anyone sugguest me the function that can solve my case?

[saravnepali]

Please Login or Register  to view this content.

If you want the time in column N, put this formula and drag down.

Also, for future please attach the spreadsheet rather then the image so that its easier to check the formula.

[baobang212]

thank you for your support, but actually what I want is calculate the time between the first message of visitor and the first response time of agent. You can see visitor always place in the first event, but not for agents, they may place in second event or the third.
For example with id =1, what I want is a function that is able to detect time of message that send by agent then subtract it with the time of event 1.
Also, thanks for your advice, I attach the file hereAttachment 654951

[AliGW]

You need this:

=OFFSET($A2,0,MATCH("Agent",A2:M2,0))-E2

However the entry in E2 is a date and time - this needs changing to just a time.

[Glenn Kennedy]

or (to avoid the OFFSET function which recalculates every time ANTHING is changed - and which can cause performance issues in big datasets):

=INDEX(2:2,,MATCH("agent",2:2,0)+1)-E2