Forum Discussion
Need Y'all's Help Again
I don't know it errored, just gave a long error, so I SSed what I typed and pasted it into the post.
7 Replies
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.
I have checked one group for a day and for every time I have checked so far, this has worked!!! Thank you!!!
I am going to go through later and hand check more later, but seems good so far!
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
This seems to only check the start time, not the end time?
Check all:
as you can see, the 0000 is in all rows, including the first and the last, where in the first end at midnight, and in the last midnight is the start hour, and the cell that display 5 is the result of formula.
Thanks for feedback
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:
I get where you are going with this, but for entry purposes and format, it has to be HHMM (23:59 max time), not able to have the : typed or showing. I appreciate your time though!
