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 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
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
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.
