Advanced: Formula to calculate elapsed time from 2 given time, based on a string which is not unique

[behrooz amiri]

Formula to calculate elapsed time from 2 given time, based on a string which is not unique

Dear All Excel Masters!
Hey..

I recently faced a really interesting, exciting and useful-to-others challenge, and it turned out that I need help from someone better than me! :) a real MASTER!
Let's say an Excel workbook which has 2 sheets : "Sent" and "Inbox" contains data which is coming from an Exchange Mailbox(thats right! I'm an Exchange Admin)  . the Idea is to calculate response time for a received Email that is replied! sheet's layout are as bellow :

Inbox Sheet:

Attributes.ConversationTopic	Folder Path	Subject	Sender.Name	DateTimeReceived
we got error	\Inbox\	we got error	Behrouz Amiri	4/18/2018 11:55
sapleIssue	\Inbox\	RE: sapleIssue	Behrouz Amiri	4/16/2018 16:51
test03	\Inbox\	test03	Behrouz Amiri	4/16/2018 14:31
sapleIssue	\Inbox\	sapleIssue	Behrouz Amiri	4/16/2018 14:31
test1	\Inbox\	test1	Behrouz Amiri	4/16/2018 14:30

sent sheet:

Attributes.ConversationTopic	Folder Path	Subject	Sender.Name	DateTimeSent
sapleIssue	\Sent Items\	RE: sapleIssue	infra test	4/18/2018 23:59
di nemune imale farsi	\Sent Items\	di nemune imale farsi	infra test	4/21/2018 16:40
test	\Sent Items\	test	infra test	4/21/2018 16:36
we got error	\Sent Items\	RE: we got error	infra test	4/18/2018 12:05
test03	\Sent Items\	RE: test03	infra test	4/16/2018 14:32
sapleIssue	\Sent Items\	RE: sapleIssue	infra test	4/16/2018 14:32
test1	\Sent Items\	RE: test1	infra test	4/16/2018 14:32

Some IMPORTANT Notes:

• I have tried really hard, but I couldn't do this. I also started a thread HERE and got an answer, but since I simplified the question, the correct answer couldn't be applicable for this real challenge.

• the only important fields are Attributes.ConversationTopic and DateTime[Sent/Received]

• DateTime values are actually DateTime values that are formatted in "m/d/yyyy h:mm"

• when an email comes, it will appear in the "Inbox" sheet automatically. when user replies it, it will appear in Sent sheet. the ConversationTopic column is the first "subject" of the received email. then when this conversation continues, it causes that field not to be unique anymore. so we need to get the value of the nearest bigger (minimum value which is bigger) "DateTimeSent" for any "Attributes.ConversationTopic" in the "Inbox" table, from the "Sent" Table.

• values of DateTime are always in Z-A Sort order
• for each mail (conversation.Topic) dateTimeSent (ReplyTime) is bigger than DateTimeReceived (received mail time) which make sens!! :)A real sheet is attached for easier workaround.

[#2]

Hi

I would suggest MINIFS().

=MINIFS(Sent!$E$2:$E$8,Sent!$A$2:$A$8,A2,Sent!$E$2:$E$8,">"&E2)

[#3]

its not working, it draws a #Name error..
Any Idea?

[#4]

That is not the formula I posted.

[#5]

Hi Behrooz,

The formula in the previous conversation works just fine, but you forgot to change the column references in the formula, this is why you got a #VALUE! error.

This is the formula with the correct column references:

=IF(ISNUMBER(MATCH(A2,Sent!$A$2:$A$9,0)),
  SUM(IF(MIN(ABS(E2-IF(A2=Sent!$A$2:$A$9,Sent!$E$2:$E$9)))=ABS(E2-IF(A2=Sent!$A$2:$A$9,Sent!$E$2:$E$9)),Sent!$E$2:$E$9)),0)

Please find it in the attached file

By the way, you got a #NAME? error when you tried Detlef's formula because MINIFS function is currently only available in the Excel 2016 with Office 365.

It seems that you don't have an Office 365 subscription.

However, MINIFS does not always give you the correct result, in your case, you need to a powerful array formula.

Regards

[#6]

Dear Haytham Amairah,
Thank you for helping me out, AGAIN! :)

your sample file works just fine, except its returning 1 wrong time stamp.

please review in the attached file, I just highlighted it in red. I tried to figure it out, but your formula is too professional to be solved!

[#7]

Hi Behrooz,

Sorry about that, but can you please explain why that result of the formula is wrong?

The value returned by the formula is the nearest minimum DateTimeSent value.

[#8]

Hi, there.

Well, I'm surprised by the simplicity of the solution that I've ended up with (btw, I'm using Excel 2016):

=IF(
(   MINIFS((   Sent!$E$2:$E$9   ),(   Sent!$E$2:$E$9   ),(   ">"&$E2   ),(   Sent!$A$2:$A$9   ),(   $A2   ))   )=0,
"Not replied",
(   MINIFS((   Sent!$E$2:$E$9   ),(   Sent!$E$2:$E$9   ),(   ">"&$E2   ),(   Sent!$A$2:$A$9   ),(   $A2   ))   )
)

MINIFS returns the minimum of DateTimeSent WHERE DateTimeSent is greater than DateTimeReceived AND Inbox!Conversation = Sent!Conversation.

If the criteria excludes all records, MINIFS returns '0' so we can use that to indicate that a reply is yet to be sent.

Example file attached.

It includes a formula using INDEX/MATCH that incorporates MINIFS. the INDEX/MATCH approach is where I started for a solution, later realizing that MINIFS worked fine on it's own.

Hope that helps.

[#9]

@behrooz amiri, I've just realized that my solution is the same as @Detlef Lewin's.

[#10 · Accepted solution]

Hi Behrooz,

In fact, I missed to consider this point that you mentioned (sorry about that!):

for each mail (conversation.Topic) dateTimeSent (ReplyTime) is bigger than DateTimeReceived (received mail time) which make sens!! :)

So please get rid of my previous formula, and use this one instead:

=IF(MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9))=0,"",
MIN(IF((Sent!$A$2:$A$9=A2)*(Sent!$E$2:$E$9>E2),Sent!$E$2:$E$9)))

And please find it in the attached file.

Note: This formula is compatible with all versions of Excel, but if you decided in the future to get an Office 365 subscription, or to upgrade to the upcoming Office 2019, I advise you to replace it with the formulas suggested by @Detlef Lewin and @Deleted.

Their formulas are easier and don't require you to press Ctrl+Shift+Enter to get the correct result.

Regards

[#11]

As a comment.

One more variant of that formula which doesn't require CSE is

=IF(AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1)=0,"",
AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1)
)

or simply

=IFERROR(AGGREGATE(15,6,1/(Sent!$A$2:$A$9=A2)/(Sent!$E$2:$E$9>E2)*Sent!$E$2:$E$9,1),"")

if apply custom format

yyyy-mm-dd hh:mm;@

to the resulting cells