Issue with formula and before/after midnight

Question

I am struggling with the last part of my sheet. The day shift works fine but when it comes to the night shift, it doesn't work the way I want. Some data are entered using a G-form. The shifts are 7:00:00 to 19:00:00 for Day shifts and 19:00:00 to 7:00:00. The supervisor is using another G-form to quality check the entries and their initials is entered to show that. It works for Day shift and for night shift before midnight but not after midnight. The first sheet is as follows:

The second sheet is as follows:

Any idea how to fix the issue? I was thinking a formula like if after midnight, query data from before midnight but cannot really figure it out Here is the test sheet: https://docs.google.com/spreadsheets/d/1i_8Y_LEt7rBokFcBouzCaJY8zDvXkUYZiheVUJ8OD_Q/edit?usp=sharing

Answer 1

In place of the current formula in Cell_K2 of Sheet1, test this out and see how it goes:

=map(A2:A,D2:D,lambda(a,d,if(a="",,if((timevalue(a)>=time(0,0,0))*(timevalue(a)<=time(7,0,0)),to_text(d-1),to_text(d)))))