Forum Discussion

Caser29's avatar
Caser29
Copper Contributor
Aug 01, 2026

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.    
excel
Formulas and Functions
office 365
Terio's avatar
Terio
Tin Contributor
Aug 04, 2026

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

Caser29's avatar
Caser29
Copper Contributor
Aug 05, 2026

This seems to only check the start time, not the end time?

  • Terio's avatar
    Terio
    Tin Contributor
    Aug 07, 2026

    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