Excel formula to compute the time Of "Logoff" record and its nearest "Logon" rec-CodePudding

I would like to have a 3rd column that only shows the time duration if the 2nd column value is "PC LogOff". The time duration will get the 1st instance of "PC LogOn" before the "PC LogOff" occurred. Is this possible in Excel?

CodePudding user response：

XLOOKUP() will give you desired result.

=IF(B1="PC LogOf",A1-XLOOKUP("PC LogOn",$B$1:$B1,$A$1:$A1,0,0,-1),"")

CodePudding user response：

If you don't have XLOOKUP, in C2 put =IF(B2="PC LogOn",A2,C1) and drag down, with C1 being just =A1.
Then in D1 put =IF(B1="PC LogOff",A1-C1,"") and drag down.