the hours of operation of two device

I think you'll need to provide more info, what exactly do you need? It could be as simple as adding both totals together and deducting from 24 with a min in there to show zero, such as =MIN(0, total1 + total2)

but we really need more info before giving a definitive answer. Can you fill out some of the expected results?

it monitors the operating hours of two pumps, we have one pump marked with HA1 and the second pump marked with HA2. On the column marked with "Pornit", write the time when the pump was started and on the column with "Oprit", write the time when it was stopped, they are recorded in the time interval 0 to 24 hours on a single day. there are several queues because in a day the pumps are turned on several times a day depending on the needs. then it is calculated independently for each cat that worked. Now I want to know how long the pumps stopped in the time interval from 0 am to 24 am, to see how much energy was not consumed from the system. the hours of energy consumption overlap, so it cannot be calculated twice, but must be calculated only as long as both have stopped

so to drop this down to its most basic, you're looking for the total number of hours when no pumps were in operation?

ie if Pump1 was 00:00 to 18:00 and pump2 was 19:00-00:00 then the answer would be 1 (ie 18:00 - 19:00)?

or am I reading that wrong?

Originally Posted by zcytunoy

in the captures I posted several operating scenarios. I have also attached the excel file, so I am interested in the hours when energy is not consumed from the system, no matter how the form is filled out. but to be in the time interval 00:00 - 24:00

it may just be me but in none of those examples posted above or the workbook do you give an example of the answer you are expecting, you've shown example data but havent said that in example A I would expect 05:00 hours or example C works out at 3:00 hours.

Can you confirm that my example above is correct?

Also, is it possible to have part hours?

Originally Posted by zcytunoy

is not correct, I explained that it must work only in the time interval of a single day without switching to another day

My example doesnt switch to another day though?

I haven't gone through the whole exercise, but whenever I see something like this, I almost always end up looking at an algorithm like:

1) Extract all time stamps from the form, sort in order from low to high, and include the pump number and on/off toggle information with each time stamp. I usually envision a SMALL() function to extract the time stamps in order, then lookup or equivalent functions to get the pump number and on/off toggle for each time stamp.
2) Analyze that information to assign either "off to on", "on to off", "stay on", or "stay off" (if needed) to each time stamp. I would expect this to be a nested IF() function. The hard part is getting the logic right.
3) With each time stamp labeled with one of those three or four labels, then the total time on is simply "sum of the times labeled off to on" minus "sum of times labeled on to off". Total idle time is simply one (or 24:00, if you prefer) minus that result. If it is necessary to see the sexagessimal time, format as [hh]:mm, or you can leave the result in decimal days.

As indicated, I haven't been through the details. Does that algorithm seem workable? Are you able to put an algorithm like that together? What steps are you uncertain about?

Solution for users Excel 365 and 2021:

I completely solved it, please try:

Formula:

Please Login or Register  to view this content.

Solution for all Excel users with a help table:

Please try in J1: 00:00:30 and in J2 =J1+1/1440 copy down until J1440.

Please try in K1 and copy down:

Formula:

Please Login or Register  to view this content.

Solution:

Formula:

Please Login or Register  to view this content.