CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formuladate-range
S Mitra
S Mitra

Reputation: 3

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.

Upvotes: -6

Views: 148

Answers (2)

user27425627
user27425627

Reputation: 1293

Here is an alternative solution for which you don't need to add any auxiliary columns. Simply select the whole range A2:J, apply conditional formatting, and in the custom function input, like so:

Applying conditional formatting.

; enter the following formula:

=LET(start, TOCOL($D$2:$G, 1, TRUE), ROWS(QUERY({start, FILTER($J$2:$J, $J$2:$J<>"")}, "where (Col1 < datetime '" & TEXT($J2, "yyyy-MM-dd HH:mm:ss") &"' and Col2 > datetime '" & TEXT(INDEX(start, ROW($A2)-1), "yyyy-MM-dd HH:mm:ss") & "')", 0)) > 1)

Output:

Output example in spreadsheet.

References

Upvotes: 0

doubleunary
doubleunary

Reputation: 19155

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.

Upvotes: 0

Related Questions