The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel times are just numbers, SEQUENCE works well for generating times. In the first worksheet, let’s generate 9 times, one hour apart, starting at 9:00 AM. For start I’ll use 9:00 AM. For times, I use 9. And for step I use 1 hour. In cell E5, I’ll set up the SEQUENCE function to use these values. Rows is the Times value in C5. Columns is 1. Start is 9:00 AM from cell C4. And step is 1 hour, from C6. When I enter the formula, we get a list of 9 times starting at 9 AM, and ending at 5 PM. If I adjust any of the inputs, SEQUENCE outputs a new set of times. One thing I want to mention is that Excel times are stored as fractions of a day, where a day is equal to 1. If I apply general number formatting to these results, you can see the numbers Excel is actually using. If you see numbers like this when you expect to see times, make sure to apply time formatting. On the next worksheet, we have inputs set up a bit differently, so that we can use a Start and End time, and then calculate the number of times. C4 and C5 contain Start and End times. Cell C6 contains the Step value, which is currently set at 30 minutes. In cell C7, we need to enter a formula to calculate the number of times. This is the End time minus the Start time, divided by the Step time. Plus one. With current values, this returns 21. Now in cell E5, I’ll enter the SEQUENCE function. Rows is the Times value in C7. Columns is 1. Start is 8:00 AM from cell C4. And step is 30 minutes from cell C6. When I enter the formula, we get a list of 21 times. Notice I can easily adjust the Step value to 1 hour, or even 15 minutes, and the formula keeps working. Finally, I just want to mention that you can hardcode times into SEQUENCE by wrapping them in double quotes, as seen in B5. You can also use the TIME function to embed times, as seen in cell D5.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
