Find difference between times of records with multiple conditions

[jdeslarmes]

Hello community, I need to calculate the time between alarm records as expressed in the green column of the following table:

Alarm logs occur on different dates, locations and have different alarm descriptions. I need to calculate the time between the ALM type alarm and the RTN type alarm of the same location and description, and for cases where the last alarm record is of the ALM type (ID:2) I must calculate from the moment I am analyzing (in this case 02/24/2024 00:00). Thank you in advance for any kind of help you can give me.

[datagrrl]

Is the issue because of time zones? Because that is a little deeper than I can go right now.

Does this work?

Interval(Now() - Date, 'hh:mm:ss')

[jdeslarmes]

Hello datagrrl, thank you for your response, the problem is not due to the time zones, the problem is how to calculate the time for specific records when there is an ALM type alarm. For example: with ALM alarm with ID 1 I must find the RTN alarm record with ID 5 (with the same location and description) to calculate the 48 hours of elapsed time. For the ALM alarm with ID 2, I must calculate the time with today because there is no RTN alarm record with the same location and description (72hrs). For the ALM alarm with ID 3 I must find the RTN alarm record with ID 4 (with the same location and same description) to be able to calculate the 1:30 hour elapsed time and the same with the alarm with ID 6 and 7.

[datagrrl]

OKay, I was reading too fast and did not wrap my head around the location and alarm type. I will try to take another look, but it was more complex than I thought. Hope someone can help you.

[jdeslarmes]

Sorry, something I just realized is that the day of alarm ID 7 is incorrect, it should be 22/04/2024 10:00.