Many of us make (maybe from templates or manually) and use a calendar in Google Sheets to enter notes/events. Do you know how to filter such a calendar with dates and specific events to a new Sheet tab in Google Sheets?

For example, you made a calendar for the month of January 2020 in a Google Sheets spreadsheet. Under, almost, each date, you have entered job responsibilities/assignments (here in my example names of staff members).

How to filter/copy specific names under the dates to a new sheet that also with the dates (dates in number, or date format)? That’s the topic and I am going to elaborate on the same in this post.

Still having any doubt about what is the topic is about? Then please refer to the image below.

Calendar for the month of January with events (left) and the filter output on the right:

In this example, I have used a formula to copy the calendar with the name “Jon”, which is the so-called event, to the right.

Note:

I have my own free calendar templates for you to use. But, unfortunately, none of them offers extra rows below dates for entering notes.

The reason, I have used a single formula to populate a full month/year dates. You can check it here.

1. Fully Flexible Fiscal Year Calendar In Google Sheets.
2. Create a Monthly Calendar in Google Sheets in 9 Steps.
3. Free Google Sheets Calendar Template Download and How-To.

Let’s see how to filter a calendar with events to a new sheet tab in Google Sheets.

Combo Formula to Filter Calendar with Events to a New Sheet in Google Sheets

You may please note that the dates in my calendar are formatted to numbers.

I mean in cells E4, F4, etc. the content is actually the dates 01/01/2020, 02/01/2020, etc. but formatted to numbers using Format > Number > More format >Custom number formats.

Here are the tips to write a formula to copy a custom made calendar with events to a new tab conditionally.

Actually, there is no single function that can filter calendar (dates) with calendar events in Google Sheets.

Either you can use an IF+REGEXMATCH combo or an IF+SEARCH+ISNUMBER combo for this. I’ll explain to you both the combo solutions.

IF+SEARCH+ISNUMBER Combo to Filter Calendar and Events in Google Sheets

Let’s learn writing the formula for this step by step so that you can learn/understand how the combination is able to filter calendar dates and events to a new range/sheet tab in Google Sheets.

I am using the formula in the sheet where I have the calendar is. When you are using the formula in a new sheet (new tan in the same file), use sheet reference with the range like Sheet2!B4:H13 instead of B4:H13.

I will explain the following steps, which are involved in filtering a custom made calendar with events in Google Sheets, in detail.

1. SEARCH in an array to match only specific calendar events.
2. ISNUMBER in an array to match all the calendar dates without events.
3. Combine the above two to match specific calendar events and all the dates.
4. Finally, return the matching event(s) and all the dates.

SEARCH Function to Match Calendar Events in Google Sheets

To simplify things, in this tutorial, I am going to only filter one criterion (one specific calendar event), i.e. “Jon”.

If you want to include additional search strings please check this guide, Multiple Search Strings in a Single SEARCH Formula in Google Sheets, once you complete this tutorial.

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

The Search formula above returns TRUE wherever the event “Jon” appear in the calendar (range B4:H13).

SEARCH is case insensitive. So it will match “JON”, “jon”, “Jon”, etc. and even a text string that contains the substring “jon” that also case insensitive.

ISNUMBER to Match Calendar Dates in Google Sheets

In the second step, we are going to just match the calendar (dates) without events.

As you can see, in my custom calendar template, all the dates are in number format. I mean they are dates formatted to numbers (please see the second image above).

So I am using the below ISNUMBER formula to match all the ‘numbers’ in the range/calendar.

=ArrayFormula(isnumber(B4:H13))

Combine SEARCH and ISNUMBER Inside the IF Logical Function

We can combine both the SEARCH and ISNUMBER outputs (step 1 and 2 formulas) as below.

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

You will get a matrix output that contains the numbers 0 and 1.

If you are seeing some other numbers in the range J4:P13, you should format the range to numbers from the Format menu > Number > Number.

Formula to Return the Matching Event (Name) and All the Dates

See the image above. Time to replace all the numbers 1 in the range J4:P13 with the values in the corresponding cells in the range B4:H13. For that, we can make use of the IF function with the above formula.

Rewrite/modify the formula in cell J3 as below.

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

Select this output range (J4:P13) and format it to Date (Format > Number > Date).

Then, if you want, format only the dates in the range to number using the d" " custom number format. For illustration, please refer to the second screenshot on this page.

These are the steps to filter/copy calendar dates with events/notes to a new range/sheet in Google Sheets.

Now the Regex based one, which is more simple.

IF+REGEXMATCH Combo to Filter Calendar with Events in Google Sheets

Using Regexmatch it’s quite easy to match a specific string (here calendar event) and numbers together. How?

Use the below formula in cell J4.

=ArrayFormula(regexmatch(to_text(B4:H13),"Jon|[0-9]"))

This Regexmatch array formula directly replaces step 3 formula above (SEARCH and ISNUMBER combo)!

It will return TRUE/FALSE values. To return corresponding dates and events, use IF as earlier. I mean, modify the J4 formula as below.

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

Using this IF+REGEX combo also, we can filter/copy calendar dates with specific events to a new range/sheet tab in Google Sheets.

Note: Regex is a known case sensitive function. So to make the above formula case insensitive use LOWER with it as below.

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

Filter Multiple Calendar Events Using Regex

The above IF+Regex combo formula scores over the IF+SEARCH+ISNUMBER formula in flexibility.

Suppose I want to filter the calendar dates and events pertaining to “Alex” and “Jon”. So other than dates, there are two criteria (multiple events) which are “Alex” and “Jon”.

By placing a pipe symbol between the criteria (events and numbers) we can include additional criteria in Regex.

You can understand what I have meant by referring to the formula below.

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

Conclusion

I am not using a custom made calendar template as above to make a note of my every day’s important events. Because I don’t have such events happening due to my nature of the job.

But I know a few people out there using a custom calendar in Google Sheets as above for entering daily events.

Even if you are finding the above tips unworthy to you, I hope you could learn a few cool formula combinations.

That’s all on filtering a custom made calendar with events in Google Sheets. Enjoy!