Need Y'all's Help Again

Mr. m_tarler answer is a clever solution and frankly, it is much simpler than the complex SUMPRODUCT formula I mention here as an alternative approach…

Formula:

=SUMPRODUCT((AD25:AD29<>"") * (AE25:AE29<>"") *

(( (AD25:AD29 <= AE25:AE29) * (AD25:AD29 <= AD24) * (AE25:AE29 > AD24) )

+

( (AD25:AD29 > AE25:AE29) * ( (AD25:AD29 <= AD24) + (AE25:AE29 > AD24) ) )))

*There is one very important catch you need to watch out for:

Empty cells in your ranges (AD25:AD29, AE25:AE29, etc.) will be treated as 0 by Excel.

If you have time correctly stored, but displayed hhmm, a solid solution can be:

AD30
=REDUCE(0,AD25:AD29,LAMBDA(a,tλ,SUM(a,--ISNUMBER(MATCH(TEXT(AD24,"hhmm"),TEXT(SEQUENCE(,MOD(OFFSET(tλ,,1)-tλ,1)*1440+1,tλ,1/1440),"hhmm"),0)))))

But if you have a full text, the formula must be:

=REDUCE(0,AD25:AD29,LAMBDA(a,tλ,LET(rλ,LAMBDA(x,REPLACE(x,3,0,":")),SUM(a,--ISNUMBER(MATCH(AD24,TEXT(SEQUENCE(,MOD(rλ(OFFSET(tλ,,1))-rλ(tλ),1)*1440+1,rλ(tλ),1/1440),"hhmm"),0))))))

If you need any explanation, ask.

Bye

The problem is you are using the 0000 for a time AFTER 2300.  Simply use 2400 and for 0100 use 2500.

If you don't like how that looks then you could use Proper time entries like 24:00 and 25:00 and then force the time format to military time hh:mm (number format -> more -> custom -> time -> 13:30)

in the following image I entered all the times using military format but actual time entry so where you see 00:00 it was entered as 24:00 and 0:15 was entered as 24:15 and so on: