Create Custom Time Slot Sequences in Google Sheets

Are you tired of the hassle of creating custom time slot sequences in Google Sheets? Well, look no further! In this article, I will show you how to easily create custom time slot sequences using modern functions like XLOOKUP, MAKEARRAY, TOCOL, and SEQUENCE. I’ll even provide you with a named function to simplify the process.

No matter what your needs are

Whether you’re managing event signups, bookings, or simply trying to keep track of your time, creating a date sequence with custom time slots in Google Sheets is a simple and effective way to stay organized.

For example, let’s say you have 5 time slots per day for event signups for a week. You can use my formula to create the sequence and use checkboxes in the next column to indicate the signups. It’s that easy!

If you’re interested in booking slots based on days or nights, you might want to check out my popular reservation and booking calendar template. It’s free, visually appealing, and features bars for easy visualization.

I understand that your requirements may vary, so I’m providing three different custom time slot sequence formulas:

1. Only time slot sequence (array formula)
2. Plus a date sequence in the next column (array formula)
3. Both are in one column similar to timestamps (array formula)

Custom Time Slot Sequence Array Formula in Google Sheets

Let’s start with the first formula. In this example, I use 5 custom time slots per day for 30 days. However, you can use any number of time slots and days, and I’ll explain how to modify the formula accordingly.

Enter the following formula in any empty cell:

=ARRAYFORMULA(
   XLOOKUP(
      TOCOL(MAKEARRAY(5,30,LAMBDA(r,c,(r+0))),,TRUE),
      SEQUENCE(5),
      B1:B5
   )
)

Make sure to select the result range and then click Format > Number > Time to format the result as time.

Explanation

This formula will populate a custom time slot sequence with your provided time intervals for 30 days, resulting in a total of 150 rows.

Here’s a breakdown of each argument:

• search_key: The search key is a sequence of numbers in 5 rows and 30 columns. The MAKEARRAY function creates a matrix with 5 rows and 30 columns, with each value in the array equal to the row number. The TOCOL function converts the sequence to a single column in the order {1, 2, 3, 4, 5, 1, 2, 3, 4, 5, …}.

• lookup_range: The lookup range is a sequence of numbers from 1 to 5. The SEQUENCE function creates this sequence.

• result_range: The result range is the cell range containing the time slots.

The XLOOKUP function searches for the search key in the lookup range and returns the corresponding values from the result range. This allows us to repeat the set of time intervals 30 times.

To modify the formula for a different number of time slots per day and occurrences, simply change the values in the MAKEARRAY function and the SEQUENCE function.

Create Custom Time Slot Sequences with Independent Date Sequence in Google Sheets

Now let’s move on to the second formula, which includes a corresponding date sequence in a separate column.

In this example, I created a custom time slot sequence in column E and added a corresponding date sequence in column D.

To achieve this, we copy the XLOOKUP search key (the TOCOL + MAKEARRAY combination) and make a minor modification to it.

Here’s the updated search key formula:

=TOCOL(MAKEARRAY(30,5,LAMBDA(r,c,DATE(2023,10,1)-1+(r+0))),,FALSE)

Insert this formula in cell D1 (make sure that column D has no values or formulas beforehand).

Select the result range and then click Format > Number > Date to format the result as dates.

Create Date Sequences with Custom Time Slots in DateTime Format in Google Sheets

Now, let’s take it up a notch and create a date sequence with custom time slots in the datetime or timestamp format. To do this, we’ll combine the two formulas we’ve already created.

Here’s an example of how to do it:

=ARRAYFORMULA(
   TOCOL(MAKEARRAY(30,5,LAMBDA(r,c,DATE(2023,10,1)-1+(r+0))),,FALSE) +
   XLOOKUP(
      TOCOL(MAKEARRAY(5,30,LAMBDA(r,c,(r+0))),,TRUE),
      SEQUENCE(5),
      B1:B5
   )
)

Once again, format the result range as Date time.

Create Dynamic Custom Time Slot Sequence with the CUSTOMTIMESLOTS Named Function

If modifying the formulas seems daunting, don’t worry! I’ve got a solution for you. I’ve created a dynamic formula using a named function called CUSTOMTIMESLOTS.

The CUSTOMTIMESLOTS function takes three arguments: range, n, and start_date. It returns an array of datetime values, representing the sequence of time slots.

To use the CUSTOMTIMESLOTS function, you can either import it from my sample sheet here or use the syntax:

=CUSTOMTIMESLOTS(range, n, start_date)

For example, to create a sequence of 4 time slots starting on October 22, 2023, you would use the following formula:

=CUSTOMTIMESLOTS(B2:B5,4,DATE(2023,10,22))

Make sure to select the result range and then click Format > Number > Date time to format the result.

The CUSTOMTIMESLOTS function is a powerful tool for creating dynamic time slot sequences in Google Sheets. It’s perfect for scheduling appointments, managing resources, or tracking events.

That’s it! Now you have all the tools and formulas you need to create custom time slot sequences in Google Sheets. Give it a try and experience the convenience and organization it brings to your workflow.

Related: How to Count Events in Particular Timeslots in Google Sheets.