Evaluating times that cross midnight divide

[PrimePorkchop]

Please see sample attached. On the "Roster" tab you put in your base schedule, then the "breakdown" tab gives you a visual across a 24-hour timeframe.

The formula I'm using works, except when the span of time between the "Start" and "End" cross midnight divide. Example:

Start = 12:00
End = 18:00
Formula works fine

But

Start = 18:00
End = 04:00
Formula no longer works fine, only working correctly on the hours of 01:00, 02:00, 03:00, and 04:00.

Please Login or Register  to view this content.

[AliGW]

To save me reverse engineering it, please explain in words what the formula needs to do. There's probably an easier way.

[PrimePorkchop]

It's evaluating if the time in D2 falls between the "Start Time" in B3 and "End Time" in C3. If it does fall between it, it executes the xlookup. If it doesn't, it should return blank.

I thought I fixed the midnight divide issue with the

Please Login or Register  to view this content.

but that isn't working

[AliGW]

See if this works for you:

=IF(AND($C3>$B3,D$2>=$B3,D$2<=$C3),XLOOKUP($A3,INDEX(Roster,0,MATCH("Name",Roster[#Headers],0)),INDIRECT("Roster["&$B$1&"]")),IF(AND(D$2<=$B3,D$2>=$C3),XLOOKUP($A3,INDEX(Roster,0,MATCH("Name",Roster[#Headers],0)),INDIRECT("Roster["&$B$1&"]")),""))

[AliGW]

No - that's not working - sorry.

[PrimePorkchop]

Unfortunately, no, it is not working, though it did change up the results a bit. Images below

This first image is the result of the formula you suggested. Like the original formula, this one works just fine as long as the "before" and "after" times do not cross midnight.
Attachment 884684

This second image is the desired final result, showing "HA1" and "HA2" active between the hours of 18:00 and 04:00
Attachment 884685

[AliGW]

Try again:

=IF(AND($C3>$B3,D$2>=$B3,D$2<=$C3),XLOOKUP($A3,INDEX(Roster,0,MATCH("Name",Roster[#Headers],0)),INDIRECT("Roster["&$B$1&"]")),IF(AND($C3<$B3,OR(D$2>=$B3,D$2<=$C3)),XLOOKUP($A3,INDEX(Roster,0,MATCH("Name",Roster[#Headers],0)),INDIRECT("Roster["&$B$1&"]")),""))

[AliGW]

Slightly shorter:

Please Login or Register  to view this content.

[PrimePorkchop]

I had an "ah ha!" moment just now... this formula works as intended. Apologies for using up your time

Please Login or Register  to view this content.

[popipipo]

Please Login or Register  to view this content.

Try this formula with an extended table

I find the table not logic

Are columns T:AA thursday or friday?

[AliGW]

@PrimePorkchop

Did you see posts #7 and #8?

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[popipipo]

Yes i have seen it
But, like i said I find the table not logic.

And what is wrong with another option?

[AliGW]

All options are valiid!

I've amended my post for clarity - the question wasn't aimed at you. Sorry for the confusion.