Forum Discussion
Advanced: Formula to calculate elapsed time from 2 given time, based on a string which is not unique
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, 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.