PART A
There are multiple rows of data in a Google Sheet with start and end times in the format as shown in the picture. This data is already processed and filtered out from a larger data set to display only today's date. How can I highlight entire row with overlapping times in those fixed rows of data(could be maximum 30-35 rows of filtered data for any given day?
Sample Data-Sheet: https://docs.google.com/spreadsheets/d/1rgLpTlQfhWRQgPLlX4OLPzdMfinCMeftvUTcd6GJOMw/edit?usp=sharing
Desired Output:
PART B
It may also be helpful if the time which are logged-in in the above table of start and end time, can show up as grey-highlight in the below time-table(this will indicate that the time is not available OR green-highlight the time which are not yet punched to show their availability).
Time Blocks:
Thought to use vlookup formula, but that will highlight only the first encounter and any subsequent overlap may not be captured.
Then tried to use match function: =ISNUMBER(MATCH(R2,N:N,0))
. But that is not taking effect in the conditional format custom formula space.
I am open to solutions involving App Script OR in-cell conditional formatting with custom formulas.
highlight entire row with overlapping times
Condense the various "start" columns into one column like this:
=byrow(D2:G, lambda(row, if(len(join("", row)), max(row), iferror(ø))))
Then collect workers whose bookings overlap like this:
=let(
workers, arrayformula(B2:B & " (row " & row(B2:B) & ")"),
starts, K2:K,
ends, J2:J,
map(starts, ends, lambda(s, e,
iferror(join(", ",
filter(workers, row(s) <> row(starts), s < ends, e > starts)
))
))
)
Finally, use a conditional formatting custom formula rule to paint rows where an overlapping booking was found:
=len($L2)
See Solution.