google-sheetsgoogle-sheets-formuladate-range

Highlight overlapping time in Google sheet for a range of data with Start and End Time in Columns


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:

enter image description here

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:

enter image description here

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.


Solution

  • 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)
    

    screenshot

    See Solution.