Time difference

Copper Contributor

Hello all , This is my first post EVER anywhere :) 
I would like to calculate the time spent for each session . Basically RED cell - GREEN cell = BLUE cell . Now the thing is that RED cell could be the latest date/time before or at End Session , but after previous End Session and the GREEN cell is always one row after End Session .

So far I created the End Session to help me find where a session ends , which would be if there is one or more hour difference between Date/Time Start rows . That's where User stops to register basically . 

Thanks !



4 Replies



=IF($C2 = "", "", $D2-$A3)

@Sergei Baklan Hey , Thanks for the reply . Maybe I wasn't clear . The green part is one session , the red part is second session and so on . Let's take for example the Green part . In it the green cell is earliest date/time start , the red cell is the latest date/time Finish before a new session begins . SO red cell - green cell gives result in blue cell ( 9-1-23 19:13  -  9-1-23 17:45 = 01:28:00)

za forum 2.png


In E2:

=IF(C2="End Session",D2-INDEX(A:A,XMATCH("End Session",C$1:C1,,-1)+1),"")

Fill down.

So I did it , maybe it's not the right way , but i got the results that I wanted . Basically I Marked like to the first End Session I marked the number 1 , to the 2nd End Session , number 2 etc ... After That I find MINIFS from the Date/Time Start column for the session number and the MAXIFS for the Date/Time Finish for the session number . Then MAXIFS-MINIFS was the time difference . Thanks for the replies