How to Extract Multiple Time Ranges from Checkboxes in Google Sheets

Other Questions Google Sheets
1

I’m working on a work schedule in Google Sheets. Each employee has their own sheet with sections for Sunday, Monday, and Tuesday. There’s a column for clock hours and columns for each department with checkboxes.

The problem is some employees work in a department more than once a day. For example, they might work 7am-10am and 2pm-5pm in the same department.

I can use a QUERY function to get start and end times if they only work once in a department. But I can’t figure out how to get multiple time ranges when there are gaps.

Here’s what I’ve tried:

=QUERY(A4:C17, "Select MIN(A) where (C=TRUE)")
=QUERY(A4:C17, "Select MAX(A) where (C=TRUE)")

This gives me the overall start and end times, but I need each separate range. I want to get “7AM-10AM” and “2PM-5PM” as the output.

Any ideas on how to solve this? I’ve been stuck for weeks and could really use some help. Thanks!

2

As someone who’s tackled similar challenges with Google Sheets, I can relate to your frustration. Here’s an approach that might work for you:

Consider using an array formula combined with FILTER and TEXTJOIN. Something like this:

=ARRAYFORMULA(TEXTJOIN(“, “, TRUE, FILTER(A4:A17&”-”&A5:A18, C4:C17=TRUE, C5:C18<>TRUE)))

This formula looks at consecutive TRUE values in your checkbox column and combines the corresponding times. It should output something like “7AM-10AM, 2PM-5PM”.

Keep in mind, this assumes your time data is sorted and there are no gaps within a single shift. You might need to tweak it based on your exact setup.

If this doesn’t quite solve it, you could also explore custom scripts using Google Apps Script. It gives you more flexibility but requires some coding knowledge.

Hope this helps point you in the right direction!

3

hey, u could try this:

=arrayformula(iferror(split(textjoin(“,”,1,if(C4:C17,A4:A17&"-"&A5:A18,"")),",")))

it should give u separate ranges for each dept. might need to tweak based on ur setup tho. good luck!

4

I’ve encountered this issue before, and it can be tricky. One solution that worked for me involves using a combination of FILTER and SPLIT functions. Here’s an approach you might consider:

=ARRAYFORMULA(SPLIT(TEXTJOIN(“,”, TRUE, IF(C4:C17, A4:A17&“-”&A5:A18, “”)), “,”))

This formula filters the times based on your checkboxes, joins them with commas, then splits them back into separate cells. It should give you distinct time ranges like “7AM-10AM” and “2PM-5PM” in separate cells.

You might need to adjust the range references to match your sheet’s layout. Also, ensure your time data is properly formatted and sorted for best results.

If you’re dealing with a large dataset, you may want to explore more efficient methods using custom functions or Google Apps Script.