Nested IF Statement - I think

[FanofExcel18]

Trying to put together a formula to tell me the number of hours based on the following parameters:
1. Time between Online/Offline
2. Online always is first
3. How much time between Online and Offline for the day
4. Some times multiple entries for a person within a day
5. Sometimes Online will come up twice back to back. Formula would need to ignore that


[TABLE="width: 397"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Full Name[/TD]
[TD]Online/Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]8:06 AM[/TD]
[TD]John Smith[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]8:06 AM[/TD]
[TD]John Smith[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD]John Smith[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]12:47 PM[/TD]
[TD]John Smith[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]2:11 PM[/TD]
[TD]John Smith[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]4:19 PM[/TD]
[TD]John Smith[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]7:45 AM[/TD]
[TD]Jane Doe[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]7:45 AM[/TD]
[TD]Jane Doe[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]8:37 AM[/TD]
[TD]Jane Doe[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]8:37 AM[/TD]
[TD]Jane Doe[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]1:03 PM[/TD]
[TD]Jane Doe[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]1:03 PM[/TD]
[TD]Jane Doe[/TD]
[TD]Offline[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]7:57 AM[/TD]
[TD]Jim Smith[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]10:29 AM[/TD]
[TD]Jim Smith[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]05/29/2019[/TD]
[TD="align: right"]12:09 PM[/TD]
[TD]Jim Smith[/TD]
[TD]Offline[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Assuming that your data is in Columns A-D, your title row is on row 1, and your first row of data in on row 2, place the following formula in E2 and copy down for all rows:

=IF(AND(C2=C1,D2="Offline",D1="Online"),ROUND(A2+B2-A1-B1,8),"")

This will show you the duration as each one is closed ("Offline").

 

[FanofExcel18]

Assuming that your data is in Columns A-D, your title row is on row 1, and your first row of data in on row 2, place the following formula in E2 and copy down for all rows:

=IF(AND(C2=C1,D2="Offline",D1="Online"),ROUND(A2+B2-A1-B1,8),"")

This will show you the duration as each one is closed ("Offline").

This was great! I'm still struggling with:
Sometimes Online will come up twice back to back. Formula would need to ignore that

 

[Joe4]

Sometimes Online will come up twice back to back. Formula would need to ignore that

It already does. Did you try that situation?

Maybe in that situation the data doesn't look like I think it does. Can you post an example where it is not working correctly?