How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

• Automatically change your start and end times.
• Changing your step increments from a minute to, say, every five minutes or an hour.
• Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

1. A whole day list of times in 1-minute intervals
2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

24 Hr list of times in 1-minute intervals

Let’s say we want a complete list of every minute in the day starting from midnight (00:00hrs) through to 1 minute before midnight (23:59hrs). Our formula is going to look like this:

=ARRAYFORMULA(SEQUENCE(60*24,1,0,1)*(1/(60*24)))

This will give us a list of weird numbers like this:

You lied to us, Yagi! This isn’t a list of times! 

Wow! That’s a bit rough, matey-potatey. We’re not done yet.

Formatting your number sequence

Allow me to let you in on a little Google Sheets secret. Dates and time are stored in Google Sheets as a number with the date as a whole integer (left of the decimal point) and the hours, minute and seconds as a decimal number (right of the period).

We need to use formatting to convert this to your preferred time format.

Select the column you have added your time sequence to and then go to Format > Number > Custom date and time.

Here you can select 24hr time like you see in the example below or AM/PM time.

This will convert your sequence values to an actual time. I have selected 24hr time (generally best practice in Google Sheets):

Make a row of times instead of a column

You can make a row of times by swapping the first two SEQUENCE function arguments around like this:

=ARRAYFORMULA(SEQUENCE(1,60*24,0,1)*(1/(60*24)))

Just keep in mind that you are likely to get the following error:

Error
Result was not automatically expanded, please insert more columns (1140).

Unlike rows, columns will not normally expand automatically to the right. So you will have to add more columns before the formula will complete.

How the formula work

Now to the good stuff. How does the formula work?

Minutes in a day

First, we need to get the total minute in a day.

To do this we can multiply the number of hours in a day, 24, by the number of minutes in an hour, 60. Unless of course, you have got some cool Rainman skills and know that it is 1440.

= 60 * 24

We will use this calculation twice in our formula. 

SEQUENCE

Next, we use the Google Sheets SEQUENCE function to create a sequence of numbers equal to the length of total minutes in a day, or 1440.

SEQUENCE takes up to 4 arguments and we are going to use each one:

1. Number of rows: This is the total number of rows that you want in your sequence. Incidentally, SEQUENCE can create a matrix or grid of numbers for you too.
   For us, we want this to be the total number of minutes in a day.
2. Number of columns: We want only one column of times here. So we will make this argument one (1).
3. Start number: Numbers in SEQUENCE will start at 1 default. But we want our numbers to start at zero (o).
4. Step: This is the steps apart each number will be in the sequence. You can only step in whole numbers and indeed, a sequence can only consist of whole numbers unless you hack it just like we will do in a moment. Set steps to 1 integer increments.

=SEQUENCE(num rows, num cols, start num, step)

=SEQUENCE(60*24, 1, 0, 1)

This will give us a list like this:

Converting our numbers to minute values

Now that we have our sequence list of 1440, we need to transform each value in our sequence to a total portion of one. Remember, hours, minute and seconds are stored as a decimal portion of 1 ( a whole day).

Basically, we need to divide one by 1440 which will give us the portion of one a single minute makes up.

 = 1/(60 * 24) = 0.0006944444444

We then need to multiply this value by each of the numbers in our sequence. We can do this with the ARRAYFORMULA function. ARRAYFORMULA allows you to apply the calculation to a range of cells instead of a single cell.

First, we wrap our SEQUENCE formula up in our ARRAYFORMULA. Then we multiply our portion of a minute decimal value by each item in our sequence.

=ARRAYFORMULA(SEQUENCE(60 * 24,1,0,1) * (1/(60 * 24)))

Using the time sequence in Data Validation Dropdowns

I wrote about how you can use this time sequence in a data validation dropdown here:

https://yagisanatode.com/2021/12/22/calculate-the-total-duration-of-time-between-two-periods-in-google-sheets/#Creating_a_dropdown_menu_of_times

The video tutorial

Check out the video version of this tutorial below with a bonus data validation drop-down menu portion:

Selected start and end times, and intervals

For this portion of the tutorial, we are going to create a formula that will allow us to select a start and end time within a day and select the minute intervals.

The formula is a little different and a bit more complex than the previous one where we had a full 24 hr period with 1-minute intervals. I would recommend that you read the first part of the tutorial before continuing.

Now let’s dive in.

When analysing time data I like to provide a little menu option for users to change the start and end time or the time intervals. This then generates the time sequence which might also generate a graph or table for the user to review.

So while we can set this up as a single formula by replacing the menu with raw values, I will run through this tutorial presuming we have a little selection menu at the top of the page. By the end of the tutorial, you will be able to see how to adapt it to your own needs anyway.

Setup up

Create a new Google Sheets tab and set it up like this:

You or your users will be changing the values in the grey range B1:B3. You will put your formula in B6.

The formula

Take a deep breath, because it is a big one. You will notice part of the previous formula in this one too.

Add the formula into the sequence start section. In our case, this is cell B6.

How it all works

If you have been following along from the previous example, you will probably have noticed that we are using the same ARRAYFORMULA(SEQUENCE()) combo. This time around it has been tweaked slightly to accommodate changes to start and times as well as changes in steps between times. So let’s take a look.

Building the SEQUENCE function

The Number of Rows

The first argument for our SEQUENCE function is the number of rows that you want in your sequence. SEQUENCE does not take floating-point or decimal numbers so the solution here is to subtract the start time from the end time and then divide that number by the number of steps.

number of rows = (start time - end time) / steps 

Now some of these results will end up with decimal remainders that SEQUENCE will not like. To ensure that our remainders are consistent we will use the FLOOR function to ensure that the result of the calculation rounds down.

This will give us a number of rows one less than what we need so we then need to add one.

number of rows = FLOOR((start time - end time) / steps) + 1

We first need to convert all of the times listed into minutes. We can extract hours by using the HOUR function and then multiply that number by 60 to get minutes. Once we have the hours in minutes, we can extract the minutes from the time with the MINUTE function and add the hours-as-minutes portion to get our total minutes.

total minutes of a time = (HOUR(time ref) * 60 + MINUTE(time ref))

The calculation then looks like this:

Number of columns

Really challenging one, this one. Just add one.

We only want one column.

Start time

To get our start value for the sequence we need to convert the start time to minutes as we did in the number of rows formula.

total minutes of a time = (HOUR(start time) * 60 + MINUTE(start time))

In our example, the formula looks like this:

HOUR(B1) * 60 + MINUTE(B1)

Steps

Just like the start time we have to convert the time into minutes:

total minutes of a time = (HOUR(step time) * 60 + MINUTE(step time))

This results in:

HOUR(B3) * 60 + MINUTE(B3)

The end result

The resulting SEQUENCE formula then would look like this:

With a start time of 10:10, end time of 17:00 and a step of 40 minutes the results would look like this:

Adding the ARRAYFORMULA

Next, we need to get the decimal value of the total minutes of each value in our row for Google Sheets to read it as time. We do this by applying our ARRAYFORMULA function and dividing the items generated in the sequence by the total number of minutes in a day, 60 * 24 = 1440.

= ARRAYFORMULA(sequence result / 1440)

This will give us our one-minute portions of one for each of the sequenced values:

e.g. Start: 10:10, End: 17:00, Step 40min

Formatting the time values

Now select the column you have added your time sequence to and then go to Format > Number > Custom date and time.

The Video Tutorial

Check out the video if you want a talkies walkthrough.

Conclusion

That’s it!

You could then use this in conjunction with a data validation dropdown list or in table or chart building. Give it a try.

I would love to hear how you used or adapted the formula to your own project in the comments below.

 

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.