Forum Discussion

behrooz amiri's avatar
behrooz amiri
Copper Contributor
Apr 22, 2018
Solved

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 cha...
Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    May 07, 2018

    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

behrooz amiri's avatar
behrooz amiri
Copper Contributor
May 05, 2018

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!

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
May 07, 2018

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 07, 2018

    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

Resources