Filter a Custom Made Calendar with Events to a New Sheet in Google Sheets

Do you use a custom calendar in Google Sheets to keep track of your notes and events? Have you ever wondered how to filter that calendar to a new sheet based on specific dates and events? In this post, I will show you how to do just that.

The Problem: Filtering a Calendar with Specific Events

Let’s say you have created a calendar for the month of January 2020 in a Google Sheets spreadsheet. Each day has notes or events associated with it. Now, you want to filter or copy the specific events to a new sheet with the corresponding dates. Sounds complicated? Not anymore!

The Solution: Combo Formula to Filter Events

To filter a calendar with events to a new sheet tab in Google Sheets, you can use a combination of formulas. There isn’t a single function that can do this, but don’t worry, I’ll guide you through the process.

IF+SEARCH+ISNUMBER Combo

One way to filter the calendar events is by using the IF, SEARCH, and ISNUMBER functions. Here are the steps:

1. Use the SEARCH function to match specific events in the calendar.
2. Use the ISNUMBER function to match the calendar dates without events.
3. Combine the above two functions to match specific events and all the dates.
4. Finally, return the matching events and dates.

Example Formula

=ArrayFormula((iferror(search("Jon",B4:H13))>0)+(isnumber(B4:H13)))

This formula will give you a matrix output with numbers 0 and 1. Format the range to numbers if you see any other numbers in the output.

Formula to Return the Matching Events and Dates

To get the final result, replace the numbers 1 in the output range with the corresponding dates in the calendar. Use the IF function with the above formula to accomplish this.

Example Formula:

=ArrayFormula(If((iferror(search("Jon",B4:H13))>0)+(isnumber(B4:H13))>0,B4:H13,))

Format the output range to dates. You can also format the dates to numbers if needed.

IF+REGEXMATCH Combo

Another way to filter the calendar events is by using the IF and REGEXMATCH functions. This method is simpler and uses regular expressions.

Example Formula

=ArrayFormula(if(regexmatch(to_text(lower(B4:H13)),"jon|[0-9]")=true,B4:H13,))

This formula is case-insensitive and will return the matching events and dates. You can also filter multiple events using regex.

Conclusion

Whether you use a custom calendar template or create one from scratch, filtering events in Google Sheets is a useful skill to have. By using the combo formulas explained above, you can easily filter specific events to a new sheet based on dates. So go ahead, try it out, and enjoy the power of Google Sheets!

For more tips and tricks on all things Google Sheets, visit Crawlan.com.