Forum Discussion
Advanced: Formula to calculate elapsed time from 2 given time, based on a string which is not unique
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.
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
10 Replies
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.
behrooz amiri, I've just realized that my solution is the same as Detlef_Lewin's.
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
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!
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
Hi
I would suggest MINIFS().
=MINIFS(Sent!$E$2:$E$8,Sent!$A$2:$A$8,A2,Sent!$E$2:$E$8,">"&E2)
its not working, it draws a #Name error..
Any Idea?That is not the formula I posted.
